DAX to countrow if sum condition is met

[Anonymous]

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

[Greg_Deckler · Accepted solution]

You could do a SUMMARIZE by Client ID and then do a simple CALCULATE, COUNT, FILTER [Amount] > 0

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

Ah, that makes sense, glad you got it worked out!