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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors