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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vbaskar
Frequent Visitor

Grouping a Calculated Measure

Hi,

 

I have column 1 (Start Date) and column 2 (End Date). I wrote a new measure to calculate End Date (minus) Start Date and based on the result (number of weeks), I have categorized them as 0-1 Week, 1-2 Weeks, 2-3 Weeks and so on. This part is working fine. 

 

Now in a separate report, I want to group the newly calculated measure and find the number of records for each category. I then want the results to look like:

 

Category (new measure)Count of Services (Records)
0-1 Week50
1-2 Weeks60
2-3 Weeks70
3-4 Weeks60
> 4 Weeks60

 

However, when I drag the new measure I only see the first value 0-1 week and not the remaining values since this is a measure and doesn't look at row by row details. Anyway we can show the above result? Any help is greatly appreciated!

 

I realize that doing the same process using a calculated column would be straight forward. But I specifically want to try and see if I can do this using a measure. 

 

 

 

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi vbaskar,

According to your description, my understanding is that you want to group the measure result. Please refer to the following sample:

I have a table like this:

1111.png

Then I create a table used to display the Category:

2222.png

After that, we can create a measure like below:

Count of Service = var a = ADDCOLUMNS(Data, "weeks", SWITCH(DATEDIFF(Data[Start Date],Data[End Date],WEEK),0,"0-1 Week",1,"1-2 Weeks",2,"2-3 Weeks",3,"3-4 Weeks","> 4 Weeks"))

return SUMX(a,IF([weeks] = MIN(Category[Category]),1,0))

Then drag the measure and the ‘category’[category] to the table visual, the result will like below:

3333.png

Best Regards,

Teige

Thanks, @TeigeGao! That's exactly what I wanted to do.

 

By any chance, can you bring about this solution without creating a new table to display the category? We will be giving PowerBI datasets access to different teams within my company and I'd like to encourage them to write their own measures without doing a calculated column or a new table. I can add a table on the backend this time, but going forward if anyone wants to create a new measure and then group it, I am looking to see if there is a way. Thanks again for your help. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.