Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
qp_andy
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:

 

SiteCounts.PNG

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

A01 January 202010000
C01 January 20204500
A15 January 2020-5000
C15 January 2020500
D15 January 2020500
E15 January 20201000
A15 February 20202000
C15 February 2020500
D15 February 2020-500
E15 February 2020-1000
F15 February 2020200
A15 March 2020-7000
C15 March 2020600
E15 April 2020500
F15 May 2020100

 

 

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.

View solution in original post

6 REPLIES 6
qp_andy
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.

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

Greg_Deckler
Super User
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
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.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

@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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors