Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Trying to work out the DAX measure to count the rows if the sum of the amount is greater than 0 per ClientID, but to no avail.
Data is as follows:
ClientID | Amount |
1 | 0 |
1 | 10 |
2 | 0 |
3 | 100 |
Each row is a meeting, and I need to count the number of meetings in the case that the sum of the amount per Client ID is >0.
So, figure for above table would be 3 meetings.
What would be the best way to achieve this?
Many thanks!
Matt
Solved! Go to Solution.
You could do a SUMMARIZE by Client ID and then do a simple CALCULATE, COUNT, FILTER [Amount] > 0
You could do a SUMMARIZE by Client ID and then do a simple CALCULATE, COUNT, FILTER [Amount] > 0
Thanks for the pointer @Greg_Deckler
I've created a table with summarize and then counted the rows in the original table, filtering by those clientID with amounts >0.
Table = SUMMARIZE(Sheet1;Sheet1[ClientID];"Test";sum(Sheet1[Amount]))
Measure 4 = CALCULATE(COUNTROWS(Sheet1); filter('Table';'Table'[Test]>0))
I presume using an intermediary table will impact on the size of the data model? Is that the best way of doing it or can I do it all in one measure without the need for the additional table?
Thanks for your time and assistance.
Matt
You should be able to do that in a single measure, something like:
Measure 4 = VAR mytable = SUMMARIZE(Sheet1;Sheet1[ClientID];"Test";sum(Sheet1[Amount])) RETURN CALCULATE(COUNTROWS(mytable); filter('mytable';[Test]>0))
Perfect, thanks. That worked when I based the count on the original table (Sheet1) in the return calculate.
Final Measure = var SummaryTable = SUMMARIZE(Sheet1;Sheet1[ClientID]; "Test"; sum(Sheet1[Amount])) RETURN CALCULATE(COUNTROWS(Sheet1); filter(SummaryTable; [Test] > 0))
Once again thanks for your help.
Matt
Ah, that makes sense, glad you got it worked out!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |