Best formula for multiple if conditions

DAX DAX Calculations
1

Dear All,

I am struggling with a formula that takes into account different conditions. I have tried with if and switch and i cant get the right result. Is there something else in DAX that I can use?

With IF:
KPI 6B =
IF (
    pre_abcactivitystructure[KPI 6A] = "Y",
    "N",
    IF (
        AND (
            pre_abcactivitystructure[abc_AccountRegionId] <> 166210017,
            pre_abcactivitystructure[abc_AccountRegionId] <> 166210018
        ),
        "N",
        IF (
            AND (
                pre_abcactivitystructure[abc_FundingSourceId] = "BILA",
                pre_abcactivitystructure[abc_Finance_LegalForm] = "2 {Profit}"
            ),
            "N",
            IF (
                pre_abcactivitystructure[abc_FundingSourceId] = "EU",
                "N",
                IF (
                    pre_abcactivitystructure[abc_FundingSourceId] = "ESA",
                    "N",
                    IF (
                        pre_abcactivitystructure[abc_FundingSourceId] = "US-GOV",
                        "N",
                        IF (
                            pre_abcactivitystructure[abc_AccountName] = "VITO NV",
                            "N",
                            IF (
                                pre_abcactivitystructure[abc_Finance_AccountKey] = "abc",
                                "N",
                                IF ( pre_abcactivitystructure[abc_Finance_LegalForm_Code] = "44", "N", "Y" )
                            )
                        )
                    )
                )
            )
        )
    )
)

With SWITCH:
KPI 6B =
SWITCH (
    TRUE (),
    pre_abcactivitystructure[KPI 6A] = "Y", "N",
    AND (
        pre_abcactivitystructure[abc_AccountRegionId] <> 166210017,
        pre_abcactivitystructure[abc_AccountRegionId] <> 166210018
    ), "N",
    AND (
        pre_abcactivitystructure[abc_FundingSourceId] = "BILA",
        pre_abcactivitystructure[abc_Finance_LegalForm] = "2 {Profit}"
    ), "N",
    pre_abcactivitystructure[abc_FundingSourceId] = "EU", "N",
    pre_abcactivitystructure[abc_FundingSourceId] = "ESA", "N",
    pre_abcactivitystructure[abc_FundingSourceId] = "US-GOV", "N",
    pre_abcactivitystructure[abc_AccountName] = "VITO NV", "N",
    pre_abcactivitystructure[abc_Finance_AccountKey] = "abc", "N",
    pre_abcactivitystructure[abc_Finance_LegalForm_Code] = "44", "N",
    "Y"
)

Thanks in advance!

Best regards!
Aracelli

2

These are your two options really.

Doesn’t seemed to me like the SWITCH is setup correctly though just by looking at it.

image
image.png774×224 8.33 KB

How about placing each individual part of this in variables, then simplify you SWITCH a bit.

You can place TRUE/FALSE logic into a variable,

For example this could be a variable

pre_abcactivitystructure[KPI 6A] = “Y”

and this

pre_abcactivitystructure[abc_AccountRegionId] <> 166210017

I’ve completed something similar here

Once you’ve done this, I would also test one by one each part of the formula to see what is not performing as it should.

Thanks
Sam