Display multiple or duplicate records on Power BI dashboard
Why the need to show multiple records on a Power BI dashboard?
Most of the time, data used in Business Intelligence dashboards is gathered from multiple sources. So, there is a significant chance of duplication in records and primary (or composite) key formation. In this blog, you’ll learn how to see multiple or duplicate records based on that entity’s primary key.
With this approach, a dashboard will display the multiple or duplicate records and the use will be able to add slicers to filter records based on the count. This will help them delete or correct those records at the backend level.
For instance, let’s assume these records are visible on one of the Power BI report pages itself. In that case, we will have a consistent look at data sanity and therefore ensure the accuracy of the data we receive directly from the data source.
Let’s understand the process step-by-step using an example dataset.
Step 1: Select the dataset
Here for reference, consider the Employee dataset named ‘Employee_Data,’ which is pulled from a Synapse data warehouse (you can use any data source of your choice).
Step 2: Redevelop the dataset
Pull the same Employee dataset for redeveloping the ‘Employee_Count’ table through the following steps in Power BI.
Now, for ‘Employee_Count’ perform the following activities:
-
- In Power query editor mode, click on the Transform tab.
- Then click on the first Group By option.
- A dialog box will appear where you will mention the column to group by. For example, if you want to group by Employee with the same first names, select that specific field in the column section. In this example, our goal is to display multiple records, and which are uniquely identified using the primary key called ’PK_key’ so we perform ’Count Rows’ operation and name that column as ’Count’.
- Save your required inputs and selections.
Based on the requirements, you can explore the advanced section of this “Group By“ dialog box as well.
Step 3: Create relationship between data sources
Once our two data sources are ready, ‘Employee_Count’ and ‘Employee_Data,’ we will connect them together through the relationship shown below.
Our ultimate objective is to plot it across a dashboard so that we can continuously monitor the data quality.
We can explore this data visualization domain depending on our requirements. In this example, the dashboard visualizes the following things:
-
- Displays ‘Count’ from ‘Employee_Count’ and rest from others’ Employee_Data’ table as a main visual table.
- The left side top displays a card that pulls a measure that shows multiple total records.
- Follows slicers help select records based on the duplicity count of records, active/closed records, and other detailed filters.
Conclusion
Using this technique, Power BI report users can make quick decisions about data clean-up tasks ensuring reliable data and results based on what they see in the dashboard. Once they display multiple or duplicate records in their dashboard report, they can constantly look at data sanity. It will later help them delete or correct those records at the backend level.
This technique of constructing relationships within existing table schema and performing the above-mentioned activities to display multiple records in the Power BI dashboard is quite generic. It can be used by any user, for specific data projects, or for any internal reporting needs.
We have helped many clients by integrating such cutting-edge features helping them secure their data accuracy and maximizing the potential of their reporting capabilities. If you’re facing challenges due to data duplication within your records, feel free to contact us!
References:
- Concept of GroupBy can be referred in detail from here
- DAX expression that counts table rows: https://learn.microsoft.com/en-us/dax/best-practices/dax-countrows
- More information on many-to-many relationship between tables: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many
- About Power platform
