Forum Discussion

btwilkins's avatar
btwilkins
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
btwilkins's avatar
btwilkins
Frequent Visitor
6 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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:

https://www.daxformatter.com/

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    If it won't post in DaxFormatter, it won't work. It doesn't allow syntax errors. Best site out there for DAX IMHO.😁

  • btwilkins's avatar
    btwilkins
    Frequent Visitor
    6 years ago

    Ok, Code is no clean but getting the aggregation issue now for the "sector" column... 

     

    "Also, if this is a measure, you will need to wrap column references in aggregators like MAX, MIN, SUM, etc."

     

    How do I do this when it is a text field? 

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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. 

    • btwilkins's avatar
      btwilkins
      Frequent Visitor
      6 years ago

      Just tried this which seems slicker, and had the same issue;

       

      ZZ TfW PTL ODP =
      SWITCH (
          TRUE (),
          'Fct SG PTL ODP Period'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Sector] = "WCB"
              && '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'[Service Group] = "HL05", 'Fct SG PTL ODP Period'[TfW SG Performance Mins Period] * 0.05
      )