topic Re: Using a measure as a filter for another measure in DAX Commands and Tips

Using a measure as a filter for another measure

qp_andy — Tue, 28 Apr 2020 19:29:32 GMT

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

Re: Using a measure as a filter for another measure

Greg_Deckler — Tue, 28 Apr 2020 19:46:26 GMT

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.

Re: Using a measure as a filter for another measure

qp_andy — Tue, 28 Apr 2020 21:24:59 GMT

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?

Re: Using a measure as a filter for another measure

qp_andy — Wed, 29 Apr 2020 09:02:35 GMT

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

Re: Using a measure as a filter for another measure

qp_andy — Mon, 04 May 2020 09:15:45 GMT

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

Re: Using a measure as a filter for another measure

MattAllington — Tue, 05 May 2020 01:57:07 GMT

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

Re: Using a measure as a filter for another measure

qp_andy — Tue, 05 May 2020 02:35:24 GMT

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