Dax Formula Help

[nmck86]

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 Type	Investment Priority	Corporate Focus Area	Select Applicable Investment Priority
1	LCD	Community Impact
2	LCD	Environmental
3	Internal Proposal	N/A	Executive
4	Economic Mobility
5	Environmental
6	Community Impact
7	LCD	N/A		Environmental
8	Internal Proposal		Corporate

[kcantor]

@nmck86

Can you share with us the measure you are using?

[nmck86]

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

[GilbertQ]

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?

[nmck86]

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

[Sean · Accepted solution]

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

 

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

[nmck86]

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!