Forum Discussion
Novice; IF nested in DAX - Help!
Hello
So I'm new to DAX and I come from an EXCEL background so loads my code involves nested IF functions to apply different conditions to specific field definitions.
So, for example;
there are two sectors; "CVL" and "WCB"
and 7 service groups; HL02 - HL08 (HL05 is the only group of services that exists in both, but has a different treatment in each;
Sector services group performance minute multiplier
CVL HL05 0.5
WCB HL02 0.15
WCB HL03 0.1
WCB HL04 0.2
WCB HL05 0.05
WCB HL06 0.25
WCB HL07 0.05
WCB HL08 0.15
So my nested IF would look like this ('Fct SG PTL ODP Period' is my fact table);
ZZ TfW PTL ODP ?
IF('Fct SG PTL ODP Period'[Sector]-"WCB",
IF('Fct SG PTL ODP Period'[Service Group]-"HL02",
[TfW SG performance minute period]*0.15,
IF('Fct SG PTL ODP Period'[Service Group]-"HL03",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.1,
IF('Fct SG PTL ODP Period'[Service Group]-"HL04",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2,
IF('Fct SG PTL ODP Period'[Service Group]-"HL05",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05,
IF('Fct SG PTL ODP Period'[Service Group]-"HL06",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25,
IF('Fct SG PTL ODP Period'[Service Group]-"HL07",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05,
IF('Fct SG PTL ODP Period'[Service Group]-"HL08",
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15,
'TfW SG Performance Mins Period'*0.5)
How do I get DAX to replicate this? Won't I recognize [Sector] or [Service Group] as valid fields??
Hi,
Try the following measure:
ddd = SWITCH ( TRUE (), SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL02", [TfW SG performance minute period] * 0.15, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL03", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.1, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL04", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.2, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL05", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.05, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL06", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.25, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL07", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.05, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL08", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.15, 'TfW SG Performance Mins Period' * 0.5 )
1 Reply
- MFelixSuper User
Hi,
Try the following measure:
ddd = SWITCH ( TRUE (), SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL02", [TfW SG performance minute period] * 0.15, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL03", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.1, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL04", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.2, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL05", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.05, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL06", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.25, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL07", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.05, SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Sector] ) = "WCB" && SELECTEDVALUE ( 'Fct SG PTL ODP Period'[Service Group] ) = "HL08", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.15, 'TfW SG Performance Mins Period' * 0.5 )