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
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;....)

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

 

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

Top Solution Authors