Forum Discussion
Newbie; Nested IF in DAX - Help!
Hi
So I am new to DAX and come from an EXCEL background so loads of my code involved Nested IF functions to apply different conditions to specific field definitions.
So for example;
there are two sectors; "CVL" & "WCB"
and 7 service groups; HL02 - HL08 (HL05 is the only service group to exist in both but has differing treatment in each;
Sector Service 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 Mins 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,
'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.5))))))))
How do I get DAX to replicate this? It won't let me recognise either [Sector] or [Service Group] as valid fields??
Man, at least make an effort to post cleanly formatted code. Here is your code cleaned up:
ZZ TfW PTL ODP = SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Sector])="WCB”, SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Service Group]="HL02",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15, 'Fct SG PTL ODP Period'[Service Group]="HL03",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.10, 'Fct SG PTL ODP Period'[Service Group]="HL04",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2, 'Fct SG PTL ODP Period'[Service Group]="HL05",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL06",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25, 'Fct SG PTL ODP Period'[Service Group]="HL07",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL08",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15 ), 'Fct SG PTL ODP Period'[Sector]="CVL",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05 )It looks like there is an extra ) in there so maybe:
ZZ TfW PTL ODP = SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Sector]="WCB”, SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Service Group]="HL02",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15, 'Fct SG PTL ODP Period'[Service Group]="HL03",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.10, 'Fct SG PTL ODP Period'[Service Group]="HL04",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2, 'Fct SG PTL ODP Period'[Service Group]="HL05",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL06",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25, 'Fct SG PTL ODP Period'[Service Group]="HL07",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL08",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15 ), 'Fct SG PTL ODP Period'[Sector]="CVL",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05 )Please bookmark:
Doesn't matter. MAX/MIN are ways to convert a column of values to a scalar value. Even if your "cell" only has one value, it is still a column in Power BI. So MAX() will just convert it to scalar.
9 Replies
- az38
Community Champion
Hi btwilkins
it's difficult to understand something in your statement but try switch function 🙂
like here. also read https://docs.microsoft.com/en-us/dax/switch-function-dax
Column = SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Service Group]="HL08", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15, 'Fct SG PTL ODP Period'[Service Group]="HL07", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.5 )Right so this is what I coded;
ZZ TfW PTL ODP =SWITCH(TRUE(),'Fct SG PTL ODP Period'[Sector])="WCB”,SWITCH(TRUE(),'Fct SG PTL ODP Period'[Service Group]="HL02",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15,'Fct SG PTL ODP Period'[Service Group]="HL03",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.10,'Fct SG PTL ODP Period'[Service Group]="HL04",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2,'Fct SG PTL ODP Period'[Service Group]="HL05",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05,'Fct SG PTL ODP Period'[Service Group]="HL06",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25,'Fct SG PTL ODP Period'[Service Group]="HL07",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05,'Fct SG PTL ODP Period'[Service Group]="HL08",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15),'Fct SG PTL ODP Period'[Sector]="CVL",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05)And this is the error;Error Message:
The extension measure 'Fct SG PTL ODP Period'[ZZ TfW PTL ODP] contains an invalid DAX expression. The expression is missing a '"' at line 11 position 99.- Greg_Deckler
Man, at least make an effort to post cleanly formatted code. Here is your code cleaned up:
ZZ TfW PTL ODP = SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Sector])="WCB”, SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Service Group]="HL02",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15, 'Fct SG PTL ODP Period'[Service Group]="HL03",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.10, 'Fct SG PTL ODP Period'[Service Group]="HL04",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2, 'Fct SG PTL ODP Period'[Service Group]="HL05",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL06",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25, 'Fct SG PTL ODP Period'[Service Group]="HL07",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL08",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15 ), 'Fct SG PTL ODP Period'[Sector]="CVL",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05 )It looks like there is an extra ) in there so maybe:
ZZ TfW PTL ODP = SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Sector]="WCB”, SWITCH(TRUE(), 'Fct SG PTL ODP Period'[Service Group]="HL02",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15, 'Fct SG PTL ODP Period'[Service Group]="HL03",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.10, 'Fct SG PTL ODP Period'[Service Group]="HL04",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.2, 'Fct SG PTL ODP Period'[Service Group]="HL05",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL06",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.25, 'Fct SG PTL ODP Period'[Service Group]="HL07",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05, 'Fct SG PTL ODP Period'[Service Group]="HL08",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.15 ), 'Fct SG PTL ODP Period'[Sector]="CVL",'Fct SG PTL ODP Period'[TfW SG Performance Mins Period]*0.05 )Please bookmark:
- edhans
Use the SWITCH() function. See this article on how it works. It is much MUCH easier to use than nested if statements.
- Greg_Deckler
First, don't do that. Use SWITCH(TRUE()...) construct at least. Or potentially nested SWITCH statements but I would avoid those.
Also, if this is a measure, you will need to wrap column references in aggregators like MAX, MIN, SUM, etc.
