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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hansolo
Helper I
Helper I

Equivalent to Set Analysis

Hey there - I am new to Power BI but not BI. I consider myself an advanced Qlik user and I have dabbled with other tools such as Tableau, Spotfire and have general knowledge of programming languages and data concepts.

 

I anticipate having a TON of questions about Power BI but right now I am trying to figure out how to write custom measures in Power BI. In Qlik they have a language called set analysis which allows you pass conditions into your formula, i.e:

 

SUM({< Condition = '1' }>} Value) / SUM (Value) - This would give me a percentage value for '1'

 

This provides a really easy and flexible way to start putting dashboards and intelligence together.

 

Maybe I am missing something but I havent yet seen anything like this in Power BI. I know that Power BI has DAX and R capabilities which make it very extensible, so I was hoping someone could point me in the right direction.

 

Thanks! 

 

- dave

1 ACCEPTED SOLUTION
chrisu
Responsive Resident
Responsive Resident

From your description, I think what you need is the CALCULATE function.  For a description of CALCULATE, see http://www.powerpivotpro.com/2014/03/becoming-one-with-calculate/ and https://msdn.microsoft.com/en-us/library/ee634825.aspx.  Your formula would look something like:

 

NameofMeasure = CALCULATE(SUM([Value]),[Condition]=1) / SUM([Value])

View solution in original post

3 REPLIES 3
chrisu
Responsive Resident
Responsive Resident

From your description, I think what you need is the CALCULATE function.  For a description of CALCULATE, see http://www.powerpivotpro.com/2014/03/becoming-one-with-calculate/ and https://msdn.microsoft.com/en-us/library/ee634825.aspx.  Your formula would look something like:

 

NameofMeasure = CALCULATE(SUM([Value]),[Condition]=1) / SUM([Value])

Smoupre helped as well but Chrisu gave me something exactly like set analysis. I am sure as I get more comfortable with BI I will stop relating it to Qlik but this is really helpful for me to start conceptually understanding Power BI.

 

Thanks guys!

Greg_Deckler
Super User
Super User

The equivalent in DAX for the formula you present is something along the lines of:

 

MyMeasure = SUM([Value])/CALCULATE(SUM([Value]),ALL(Table))

I'm doing that from memory so there may be a syntax error or two but basically you put this measure in a table with the column where you have the conditions and the measure will be automagically filtered to that row except for the denominator, which has the ALL clause so it will include everthing in "Table". 

 

Can't really help more without sample data, relationships, etc.

 

If you really want to make it only applicable to "1", then you would do this:

 

MyMeasure = CALCULATE(SUM([Value]),[Condition] = "1")/CALCULATE(SUM([Value]),ALL(Table))

But I think a measure as described above would ultimately be the better route.


@ 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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.