Grouping a Calculated Measure

[vbaskar]

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 Week	50
1-2 Weeks	60
2-3 Weeks	70
3-4 Weeks	60
> 4 Weeks	60

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. 

[TeigeGao]

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:

Then I create a table used to display the Category:

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:

Best Regards,

Teige

[vbaskar]

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.