Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi. I am fairly new to Power BI. I can create the visualisations and some simple measures so far, but got stuck into a problem that seems simple, but I can find a direct answer to. I upload a spreadsheet to Power BI with the number of students by date (day, month and year) with ID, region and status. I use the region field as a slicer to see the distinct count by region for all the fields, for example, if they completed the course or not. I managed to create an individual sum of all fields by whichever status I need (below), but everything I add doesn't show in the slicer, as it's not part of that table. So, I think I need to add some calculated rows to the data to be able to see in the slicer? I am displaying the data as a line chart (time series).
Total ID 1 =
CALCULATE( DISTINCTCOUNT('Table01'[ID]),
ALL('Table01'[Completion Status]) )
My question is how do I create calculated rows after data was imported into Power BI that will do a distinct count for the data for all regions by date, that I call "National" and also group a special region called "North districts" that includes just 3 of those regions? (both should appear as an option in the slicer to be selected, the new National and North Districts, along with the rest of the districts).
So the raw data upload would have 12 regions, then once in Power BI, it would sum the distinct count of all regions and create a National row with the same dates that are available for the other regions that I can select on the slicer and a custom group with 3 regions that I can also select from the slicer (summing those 3 regions distinct counts too). Then every time I update the data, it updates all of those automatically. I don't have a number column, I am counting the number of distinct IDs I have in the data. Short example:
Slicer for time series to select: National | North districts | Nicefields | Badfields | Worsefields
[Line chart]
Example of raw data:
Completion Date | ID | Name | Region | Completed Status |
25/03/2022 | YY66 | Aaron | Nicefields | Pass |
21/05/2023 | YU78 | Maria | Badfields | Fail |
15/02/2023 | TY43 | Martin | Nicefields | Pass |
16/07/2023 | YT56 | Heron | Worsefields | No show |
10/09/2022 | HY67 | Hercules | Badfields | No Show |
I'm sure there's a smarter way to do this. If anyone could help, it would be appreciated.
Thank you in advance.
Solved! Go to Solution.
Hi @maxwill83 ,
Try to create a definition table for your region and connect it to the original table.
So that you can put them into the slicer and see all the options you want.
Hi @maxwill83 ,
Try to create a definition table for your region and connect it to the original table.
So that you can put them into the slicer and see all the options you want.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.