Using a measure as a filter for another measure

[qp_andy]

Hi

In the example below, I'm trying to create a measure to count the number of SiteRefs where the cumulative value of size is greater than 0:

So the correct answer would be 4 for January, 3 for February, 2 for March and so on. I have a date table and have created measures for site size and cumulative site size (below), and am trying to create a third calculated count measure using cumulative site size > 0 as a filter, but keep getting an error (cannot use values of tru/false). Can anyone help? 

Site Size = SUM(Sites[Size])

Site Cumulative Size = CALCULATE([Site Size],FILTER(ALL('Date'),'Date'[Date] <= MAX('Date'[Date])))

Data:

SiteRefEventDateSize

A	01 January 2020	10000
C	01 January 2020	4500
A	15 January 2020	-5000
C	15 January 2020	500
D	15 January 2020	500
E	15 January 2020	1000
A	15 February 2020	2000
C	15 February 2020	500
D	15 February 2020	-500
E	15 February 2020	-1000
F	15 February 2020	200
A	15 March 2020	-7000
C	15 March 2020	600
E	15 April 2020	500
F	15 May 2020	100

[qp_andy]

@MattAllingtonany ideas? I thought this would be simple to solve although its proving much more difficult than I thought

[MattAllington · Accepted solution]

You should never attempt such a problem without  a dimension table on the site.  

Try this  

Measure = SUMX(VALUES(UniqueSites[Site]),if([Site Size Cumulative] >0,1,0))

Note the new dimenstion table called UniqueSites

https://www.dropbox.com/s/bteziytyavesd33/site%20size.pbix?dl=1

[qp_andy]

Fantastic @MattAllington this is perfect, exactly what I needed! Thank you so much.

[Greg_Deckler]

Not sure I am 100% understanding but typically you would do something along the lines of:

VAR __Table = 'Table' //current table rows in context
VAR __Table1 = ADDCOLUMNS(__Table,"Measure",[Some measure])
RETURN
COUNTX(FILTER(__Table1,[Measure] > 0),[Column])

Something along those lines, sometimes you need to do a SUMMARIZE for your initial table.

[qp_andy]

Thanks Greg. Sorry probably wasn't 100% clear. The matrix in the original post shows the cumulative values by month for each SiteRef. What I am trying to do is come up with a way to count the number of SiteRefs in each month that have a cumulative size greater than zero.

Not sure what you are referring to by [column] in your reply?

[qp_andy]

@Greg_Deckler following your advice I've got part of the way there! I've created a measure which works without context....what I cannot work out is why an error is produced when context is added

Sites 

Regards

Andy