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
  • Greg_Deckler's avatar
    Greg_Deckler
    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
    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

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.