Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"))
Solved! Go to 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
In general:
ColumnMeasure = IF( <Condition>, <True>, IF ( <Condition>, <True>, IF ( <Condition>, <True>, <False> ) ) )
Or use SWITCH.
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.
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE PARTS"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "KSS UNITS SERVICED"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "ANCILLARIES"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FIRE REG BOOKS"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "PFE COVERS"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "SIGNS"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD UNITS SERVICED"))
CALCULATE(SUM(KPIGroups_SalesStats[SALES QTY]), KPIGroups_SalesStats[KPI GROUP] = "FAD BATTERIES"))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |