if statements

[brianhackett5]

Hi all, 

Could someone tell me how the below expression should be written so as to have a number of different IF Statements in it. When using one if statement it works perfectly but won't let me use anymore than one IF statement. I've spent all day trying to figure it out so any help would be greatly appreciated. 

Thanks in advance 🙂 

TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 1 PORTABLE MAINTENANCE PARTS", CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"))

[Greg_Deckler]

In general:

ColumnMeasure = IF( <Condition>, 
                                            <True>,
                                            IF ( <Condition>,
                                                           <True>,
                                                            IF ( <Condition>,
                                                                           <True>,
                                                                           <False>
                                                                 )
                                                )
                                   )

Or use SWITCH.

[brianhackett5]

Thank you for your reply, I don't fully understand it unfortunately (im pretty new to Power BI).

I have 8 if statements which perfectly one at a time but I can't use anymore than one (i think it has something to do with the calculate function. I've posted the 8 if statements below. 

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 1 PORTABLE MAINTENANCE PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 2 RSS SYSTEM PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 3 PFE ANCILLARIES",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 4 FIRE REGISTER / FAD LOG BOOK",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 5 HOSE REEL / COVERS JACKETS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 6 SIGNAGE COMPLIANCE UNIT",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "SIGNS"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 7 ELECTRICAL MAINTENANCE PARTS",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED"))

• TOTALS = IF(CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S] = "KPI 8 BATTERY REPLACEMENT",

                CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES"))

[Anonymous · Accepted solution]

Hi @brianhackett5,

For your situation, I think SWITCH function will suitable for your requirement:

TOTALS =
SWITCH (
    CONSUMABLES_PARTS_KPIs_Link[CONSUMABLES PARTS KPI'S],
    "KPI 1 PORTABLE MAINTENANCE PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"
    ),
    "KPI 2 RSS SYSTEM PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"
    ),
    "KPI 3 PFE ANCILLARIES", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"
    ),
    "KPI 4 FIRE REGISTER / FAD LOG BOOK", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"
    ),
    "KPI 5 HOSE REEL / COVERS JACKETS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"
    ),
    "KPI 6 SIGNAGE COMPLIANCE UNIT", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "SIGNS"
    ),
    "KPI 7 ELECTRICAL MAINTENANCE PARTS", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED"
    ),
    "KPI 8 BATTERY REPLACEMENT", CALCULATE (
        SUM ( KPIGroups_SalesStats[SALES QTY] ),
        KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES"
    )
)

Regards,

Xiaoxin Sheng