cancel
Showing results for 
Search instead for 
Did you mean: 
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 a 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 a 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.

@ 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!:
Mastering Power BI 2nd Edition

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

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 â__ December 6-8, 2022

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