"IF" DAX function SSAS live

[jess_v]

Hello,

DAX total newbie here.  I'm trying to do a simple measure.  If the unit name (the column "DIM_ORG[UNIT_NM]") is "Contoso" then show "Contoso Inc", otherwise show the orignal value of the unit name.  However, when I try t add that column to my IF statement, it does not find it.  All I see listed are measures.  

My expected function would be: 

Measure = IF(DIM_ORG[UNIT_NM]="Contoso","Contoso Inc",DIM_ORG[UNIT_NM])

I would like to point out I am using an SSAS Live connection, so I can't create columns.  I understand that some DAX functions are not available with SSAS, but I belive IF is supported. 

Any help would be appreciated.  Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=IF(HASONEVALUE(DIM_ORG[UNIT_NM]),IF(VALUES(DIM_ORG[UNIT_NM])="Contoso","Contoso Inc",VALUES(DIM_ORG[UNIT_NM])),BLANK())

[kentyler]

Unlike what you would expect in Excel, or a SQL table, a measure has no direct access to a column in a specific row. However, if you're writing a measure to execute in a table or matrix visual, there will not be a "row context" the way there would be if you were writing a calculated colum.

There are functions you can use to retreive a value from a filter context when you are using the measure as a column in a table or a matrix. You might write your measure like this: Measure = IF(SELECTEDVALUE(DIM_ORG[UNIT_NM],"")="Contoso","Contoso Inc",SELECTEDVALUE(DIM_ORG[UNIT_NM],"")). The function SELECTEDVALUE returns the value of the column reference passed as first argument if it is the only value available in the filter context, otherwise it returns blank or the default value passed as second argument. https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/ 

You might consider using a variable: VAR unit_nm = SELECTEDVALUE(DIM_ORG[Unit_NM],"")
RETURN IF(unit_nm = "Contoso","Contoso Inc", unit_num)
Using variables lets you debug, as you can "RETURN" the variable unit_num to check that it is getting the value you think it is. That often turns out to be very helpful.

I learn something every time I answer a question
I'm a personal Power BI trainer