Forum Discussion

brianhackett5's avatar
brianhackett5
Helper I
8 years ago
Solved

if statements

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    In general:

     

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

    Or use SWITCH.

    • brianhackett5's avatar
      brianhackett5
      Helper I
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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