cancel
Showing results for
Did you mean:
Frequent Visitor

## Using a measure as a filter for another measure

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

1 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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
6 REPLIES 6
Frequent Visitor

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

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

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

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

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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?

Frequent Visitor

@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

Regards

Andy

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors