cancel
Showing results for
Did you mean:
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!

6 REPLIES 6
Frequent Visitor

Hello

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)

For details ,pls see attached.

Best Regards,
Kelly
Frequent Visitor

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

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:-

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

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:-

The Measure:-

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

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!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!