Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
btwilkins
Frequent Visitor

Newbie; Nested IF in DAX - Help!

‎05-13-2020 11:01 AM

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

 

 

Labels:
Message 1 of 10
1,386 Views
0
2 ACCEPTED SOLUTIONS
Greg_Deckler
Community Champion
Community Champion
In response to btwilkins

‎05-13-2020 11:43 AM

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/



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Message 4 of 10
1,348 Views
edhans
Super User
Super User
In response to btwilkins

‎05-13-2020 01:23 PM

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Message 8 of 10
1,303 Views
9 REPLIES 9
Greg_Deckler
Community Champion
Community Champion

‎05-13-2020 11:11 AM

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.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Message 10 of 10
1,377 Views
0
edhans
Super User
Super User

‎05-13-2020 11:09 AM

Use the SWITCH() function. See this article on how it works. It is much MUCH easier to use than nested if statements.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Message 9 of 10
1,379 Views
0
az38
Community Champion
Community Champion

‎05-13-2020 11:09 AM

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
)

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Message 2 of 10
1,382 Views
btwilkins
Frequent Visitor
In response to az38

‎05-13-2020 11:31 AM

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.
Message 3 of 10
1,355 Views
0
Greg_Deckler
Community Champion
Community Champion
In response to btwilkins

‎05-13-2020 11:43 AM

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/



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Message 4 of 10
1,349 Views
btwilkins
Frequent Visitor
In response to Greg_Deckler

‎05-13-2020 12:42 PM

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? 

Message 6 of 10
1,314 Views
0
edhans
Super User
Super User
In response to btwilkins

‎05-13-2020 01:23 PM

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Message 8 of 10
1,304 Views
btwilkins
Frequent Visitor
In response to btwilkins

‎05-13-2020 12:58 PM

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
)
 
 
Message 7 of 10
1,273 Views
0
edhans
Super User
Super User
In response to Greg_Deckler

‎05-13-2020 11:46 AM

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Message 5 of 10
1,345 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All