cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davegw
Frequent Visitor

How do I summarise (group by) by the value of a measure.

This is something that I feel ought to be straightforward but I really can't get my head around it.

 

I have a measure that returns me the sum of vaues in a fact table for a given ID. In effect this gives me the number of occureneces of a given fact for a certain ID. I need to create a visual that counts the number of occrences for each value of the measure.

In SQL this would be trivial as I'd simply

SELECT Count(Distinct id), <measure result> From <table> GROUP BY ,<measure result>

 

Where <table> is a subquery.

 

My measure result only has a range of three values (1, 2 and 3) . Sample is in the picture below displayed as a table.  I just want to produce a bar graph of the count of rows fro each measure value. A calculated table is not an option as the meausre results are based on a couple of slicers and a calculated doesn't respect filter contexts.

 

Can anyone help? I can't believe there isn't a simple solution!

 

2020-07-20 18_07_51-Window.png

 

6 REPLIES 6
daveggw
Frequent Visitor

Hello

v-kelly-msft
Community Support
Community Support

Hi  @davegw ,

 

First create a table with all distinct values in Id column;

Then create a measure as below:

Measure = 
var _table=SUMMARIZE('Table','Table'[id],"count",'Table'[Count])
Return
COUNTROWS(FILTER(CROSSJOIN(DISTINCT('Table (2)'[id]),_table),'Table (2)'[id]=[count]))

Finally you will see:(by a simple sample table)

Annotation 2020-07-21 153109.png

For details ,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Thank you @v-kelly-msft from @amitchandak s response I had managed to get to a calculated table with my results in DAX studio. However, I was unsure how to figure our how to display it on a visual as a measure can only returm a Scalar. I figured I'd need some king of 'helper table' to provide me with the filter contexts necessary to display my results in the required visual. IN Sql the 'helper table' comes from the nested subquery.

 

I'm not quite there yet but thanks to your help I'm getting there, so thank you both @v-kelly-msft  & @amitchandak 

davegw
Frequent Visitor

This is not yet working for me. The difference between my problem and the soultion you offered is that you created the sample data in a table (named Table). My data is not in a static table as it is derived from a measure. I can return my measure into a VAR of type table, however I cannot then evaluate the 'Count' as my Table Variable is not being accepted. See screenshot below. I must be failing to grasp some of the concept here. I get an error like Table variable 'Data_Table' cannot be used in current context because a base table is expected:-

2020-07-21 16_01_22-Window.png

 

 

If I run in DAX studio I can prove that my table variable Data Table returns expected results:-

2020-07-21 16_08_21-Window.png

 

davegw
Frequent Visitor

I finally have a working solution (though I confess I don't fully understand why - it feels like there is some black magic here. Also, I'm not entirely sure this is an optimal solution or comes anywhere near to 'best practice' but here's what I have.

Correct Viual:-

2020-07-21 18_15_43-Window.png

 

The Measure:-

2020-07-21 18_22_15-Window.png

amitchandak
Super User
Super User

@davegw , Two examples

countX(filter(summarize(Table, Table[col1], table[Col2],"_1", [measure]),[_1] >10),[col1])

countX(summarize(Table, Table[col1], table[Col2],"_1", [measure]),[col1])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.