Forum Discussion
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
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])
3 Replies
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.
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!
