cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 an 8 times 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 an 8 times 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!:
The Definitive Guide to Power Query (M)

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.