Find records with date range in Model driven app using Advance Find

When you work in Model driven app – especially around date field, you may need to search record based on certain conditions.

Some of the options are shown in below image

The most often used filter criterias are “On”, “On or after” and “On or before”.

If we want to search records based on two date fields such as by using between operator, we need to use Advance search.

Before that, let see how “On or after” and “On or before” filter works. This is required to build our between filteration criteria.

On or after :

Consider the below records and see the published date in these records.

To get records after 1st of Aug 2021, the filteration shows three records

On or before:

To get records on or before 1st of Aug 2021, the filteration shows only one record

If we need to get record between 1st Aug to 7th Aug 2021, we need to use advanced search or using advance filteration.

Advance Search:

Click on the advance search icon on the right hand corner . By using both “On or after” and “One or before”, we can achieve the between date range functionality.

Add the date range as shown below

Result shows records within the selected range of dates.

Advance Filter:

Click on the “Advance filter” option

Select the same filteration that we have discussed above

Records will be filtered out with the date range

Looking for a video demo? I have explained the same in the video below.

Multi-Table lookup – Power Platform – PowerApps Feature – July 2021

When we are trying to create a lookup field that lets you check out media to the users.
We will have a table of Books with book publilsher details, a table of Audio offerings with audio type, and a table of Video offerings with video type.

There was no easy way to create a lookup that would pull data from all three tables at once.
We may end up with three separate lookups as shown below.

The solution is to have a single lookup, that can search data from all the media tables.

Watch this full video with a demo on this.

How to consume Dynamics AX Product Images in PowerApps

Step 1: Create a sample data entity as shown below. It uses EcoResProductImage table which holds the image container of the items.

1

Step 2: Check the odata feed in the browser. You can see the Base64 images are exposed in “MediumSize” field as shown below.

2

Step 3: Create a sample PowerApps application and add a gallery. Assign the data source to the gallery.

4.png

Step 4: Assigning the “MediumSize” to the image will not shows up the image in PowerApp. So add the below code in the image source to specify the Base64.

“data:image/jpeg;base64,” & ThisItem.MediumSize

3

 

Basic Power BI report with Cross filter functionality and Edit interaction

Level : Beginners

Agenda: This blog explains the creation of basic Power BI report using cross filter functionality and edit interactions.

16

Prerequistics : Power BI desktop, Excel sheet with input data

Downloadable Contents : Input – Excel File

 

Illustration

We are going to design Power BI report for employees. We have an excel sheet with two tables, in which table 1 has the personal information of the employee whereas table 2 will have the salary information of the employee. These two tables are linked with “Employee ID” field.

Table 1:

1

Table 2:

2

Step 1: Open the Power BI Desktop software and click “Get Data” –>  “Excel”.

3

Step 2: Open dialog box allow user to select the input file in excel sheet format. Select the input file and click OK.

4

Step 3: Navigator form appears as shown below. It allows user to select the input data from the excel workbook that was selected. Tables and sheets in the input workbook will be populated. Click “Load” button to load the data into Power BI.

5

Step 4: Select the “Relationships” button as highlighted below.

6

Step 5: The line between the table 1 and table 2 describes the relationship between them. Power BI will automatically match the column name and detects the relationship between each other. Table 1 has unique record for the employee and Table 2 has many records for each employee. So it’s a one-to-many relationship.

7

Step 6: Cross filter direction has two options – “Single” and “Both”. Let’s create a report for the “Single” cross filter direction.

8

Step 7: Navigate to the “Report” button to design the report.

9

Step 8: Drag and drop the “Tree Map” control and assign the values as shown below. Assign “Employee ID” from table 1 into “Group” property and assign “Sum of Age” in Values property.

10

Step 9: Drag and drop “Pie chart” as shown below. Assign “Legend” with “Employee ID” from Table 2 and assign “Salary” from table 2 in Values property.

11

Step 10: Click on any one boxes in the “Tree map”. You will notice that the data from Table 2 is also filtered in “Pie chart” as sub set.

12

Step 11: To change the interaction between two elements, there is a provision in Power BI to toggle the filter view. Click on Tree map and select “Format” –> Edit Interactions” option.

13

Step 12: This will show option to edit the interaction in each chart. Select the filter option in pie chart as shown below.

14

Step 13: When you select the box in “Tree map”, the pie chart will automatically filter itself instead of highlighting it.

15

Step 14: If you select a pie in Pie chart, the data in the tree map is not filtered or highlighted.

16

Step 15: This is because, the cross filter condition is assigned as “Single”. Change the filter condition as “Both” in relationship tab as shown below.

17

Step 16: Select the report design and click on a pie in Pie chart. The tree view will be automatically filtered as shown below.

18

Homework

Create a report with the same input file.  Create a pie chart for Gender and create a table below to show the list of employee name that are selected. When user selects any record in grid, the pie chart should also filter itself.

19