Reply
Frequent Visitor
Posts: 14
Registered: ‎01-23-2017
Accepted Solution

Dax Formula Help

Hi All,

 

I am working with a dataset similar to the one listed below and I am trying to create a DAX formula and have been using an IF formula and it was a formula that looked at the Proposal Type Column and said IF that field is "Internal Proposal" then pull back Investment Priority... However after that I tried to nest additional IF statements into that one that would say IF Investment Priority is "N/A", pull back Corporate Focus Area. Everytime it seems to only review the first logic and not the rest. I think I am doing the formula slightly wrong. Anyone have an idea of what formula I should use for this issue?

 

 

Request #Proposal TypeInvestment PriorityCorporate Focus AreaSelect Applicable Investment Priority
1LCDCommunity Impact  
2LCDEnvironmental  
3Internal ProposalN/AExecutive  
4Economic Mobility   
5Environmental   
6Community Impact   
7LCDN/A Environmental
8Internal Proposal Corporate 

 

2017-04-20_14-24-31.png


Accepted Solutions
Super Contributor
Posts: 1,945
Registered: ‎08-11-2015

Re: Dax Formula Help

[ Edited ]

Can you elaborate why you think its ignoring the second half?

Investment Priority Test =
IF (
    'CyberGrants Data'[Proposal Type] = "Internal Proposal -Administrators Only",
    IF (
        'CyberGrants Data'[Select the applicable investment priority ] = BLANK (),
        'CyberGrants Data'[Corporate Focus Area],
        'CyberGrants Data'[Select the applicable investment priority ]
    ),
    IF (
        'CyberGrants Data'[Proposal Type] = "Local Community Development",
        'CyberGrants Data'[Investment Priority Category ],
        'CyberGrants Data'[Proposal Type]
    )
)

Seems to work fine

 IF Statements.png

Make sure you are checking for the correct column values - meaning the conditions are spelled as they are in the column

"Internal Proposal " will not be found in the Proposal Type Column but "Internal Proposal" will be with no space at the end

View solution in original post


All Replies
New Contributor
Posts: 603
Registered: ‎08-27-2015

Re: Dax Formula Help

@nmck86

Can you share with us the measure you are using?

Frequent Visitor
Posts: 14
Registered: ‎01-23-2017

Re: Dax Formula Help

Investment Priority Test = IF('CyberGrants Data'[Proposal Type] = "Internal Proposal", 'CyberGrants Data'[Select the applicable investment priority ], IF('CyberGrants Data'[Select the applicable investment priority ] = Blank(), 'CyberGrants Data'[Corporate Focus Area]))

 

This is something similar to what I have... However, when I was running it only select applicable investment priority would pull back.. none of the other conditions if that field is blank are working... I hope this helps... 

New Contributor
Posts: 600
Registered: ‎06-24-2015

Re: Dax Formula Help

Hi @nmck86

 

What you need to do when nesting IF is to rather use the SWITCH DAX function.

 

And here is a really great blog post from Power Pivot Pro explaining how to use it.

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/ 

 

Also is this for a measure or creating a column?

Frequent Visitor
Posts: 14
Registered: ‎01-23-2017

Re: Dax Formula Help

Hi I am still working to understand switch statements... does anyone know why my formula below is IGNORING the 2nd half of my if statement around "Local Community Development" that is the issue I am running into now... I have an if statement that is working but there seems to be something wrong with my logic since it is diregarding the second half of the formula.

 

Investment Priority Test = IF('CyberGrants Data'[Proposal Type] = "Internal Proposal -Administrators Only",
    IF('CyberGrants Data'[Select the applicable investment priority ] = BLANK(),  
        'CyberGrants Data'[Corporate Focus Area],'CyberGrants Data'[Select the applicable investment priority ]),
        IF('CyberGrants Data'[Proposal Type] = "Local Community Development",
            'CyberGrants Data'[Investment Priority Category ],'CyberGrants Data'[Proposal Type]))

Super Contributor
Posts: 1,945
Registered: ‎08-11-2015

Re: Dax Formula Help

[ Edited ]

Can you elaborate why you think its ignoring the second half?

Investment Priority Test =
IF (
    'CyberGrants Data'[Proposal Type] = "Internal Proposal -Administrators Only",
    IF (
        'CyberGrants Data'[Select the applicable investment priority ] = BLANK (),
        'CyberGrants Data'[Corporate Focus Area],
        'CyberGrants Data'[Select the applicable investment priority ]
    ),
    IF (
        'CyberGrants Data'[Proposal Type] = "Local Community Development",
        'CyberGrants Data'[Investment Priority Category ],
        'CyberGrants Data'[Proposal Type]
    )
)

Seems to work fine

 IF Statements.png

Make sure you are checking for the correct column values - meaning the conditions are spelled as they are in the column

"Internal Proposal " will not be found in the Proposal Type Column but "Internal Proposal" will be with no space at the end

Frequent Visitor
Posts: 14
Registered: ‎01-23-2017

Re: Dax Formula Help

Ugh. You have to love pesky little "extra spaces" I wracked my brain on it and that was the issue and your reminder is what prompted me to verify it again :-)! Thanks so much!