cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors