Forum Discussion

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

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
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

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

Community 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
Community 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
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
  Community 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
  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
  )

Forum Discussion

Newbie; Nested IF in DAX - Help!

Related content

Newbie Question Help

Nested IF

[REPORT BUILDER Help] Nested Subtotal

Newbie question

Help with expanding nested json