cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Glaeran
Frequent Visitor

[SSAS] Measure := IF [column1] = "ABCD" then DIVIDE()... else... DIVIDE()

Hey everyone,

I have a IF condition that I need to replicate in DAX measure. It looks like that:

 

Measure := IF [column1] = 'Y' then DIVIDE(sum([col2),sum(col2)+sum(col3)) else DIVIDE(sum([col2]),sum[col2]).

Else condition of course can be hardcoded as "1" but that's not a main issue here.

 

As we know, DAX measures can't use IFs based on Columns in the model/data so what would be the best way of implementing this in DAX measure(!) [this is important as I'm working with SSAS not PBI]?

3 REPLIES 3
sturlaws
Resident Rockstar
Resident Rockstar

Depends on your need.

If you have column1 on axis or slicers you can do this:
measure = if(selectedvalue(Table[column1])="Y";......

If  you want to have the sum across all values in column, but with different calculations for them you can try this
measure = sumx(table,if(selectedvalue(Table[column1])=1;....)

Glaeran
Frequent Visitor

@sturlaws, thanks. Column1 is more of used as flag column and is not used later in the report/filters/is vislble to users at all.

So not sure whether selectedvalue() will be proper here.

 

sturlaws
Resident Rockstar
Resident Rockstar

The selectedvalue() has perhaps a bit misleading name, but these two expressions are  equivalent

SELECTEDVALUE( <columnName>, <alternateResult>)
IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)

If the filter context has narrowed the values in the column down one unique value, this value is returned, otherwise a blank or alternative value is returned: https://dax.guide/selectedvalue/

If column 1 is a flag value, and not something you will add in a report the SUMX-version is probably what will give you what you need.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!