Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
maxwill83
Regular Visitor

Adding data as rows for custom calculated view

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]) )

 

 

Annotation 2023-09-05 154843aaaa.jpg

 

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.

 

1 ACCEPTED SOLUTION
isjoycewang
Solution Supplier
Solution Supplier

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.

 

isjoycewang_0-1694073491384.png

isjoycewang_1-1694073565042.png

 

View solution in original post

1 REPLY 1
isjoycewang
Solution Supplier
Solution Supplier

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.

 

isjoycewang_0-1694073491384.png

isjoycewang_1-1694073565042.png

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.