Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!