Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hello
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.
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
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:-
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:-
@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])
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |