Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have to calculate multiple KPI's based on a KPI table that relates to a transactional table with amounts (over 800k recods, with AC FC PL PY switch, and MTD/YTD and Currency Switches). Basically each KPI refers to a group of transactions, at the same tome they can have various calculations conditions (eg. filtering other dimensions).
I tried various options but not able to get the performance i would expect.
Right now the best option is to use SUMX over the KPI table and SWITCH on each encounter of a KPI and apply the formula. The KPI formulas are calculations between the KPI's themself (eg DIVIDE(KPI_A,KPI_C).
Is there a better way to approach this situation and have a better DAX logic for the measure(s) that will also provide fast percormance? I tried with multiple calculate and switch measures, but performance is worse.
Current performance of measure using a matrix visual in PowerBI (query extracted and runed in DAX Studio):
Below current measure I use:
MEASURE ' Measures'[__mCalculateKPIGroups] =
VAR __vReturn =
SUMX (
//Filter KPI table for Management P&L Hierarchies
FILTER('KPI Groups',SELECTEDVALUE ('KPI Groups'[LEV1_Name]) = "AAA" || SELECTEDVALUE ('KPI Groups'[LEV2_Name])="BBB"),
//Get row context
VAR __vKPI_ID = 'KPI Groups'[KPIGroup_SID]
VAR __vKPI_LEV3_NAME = 'KPI Groups'[LEV3_Name]
VAR __vKPI_LEV2_NAME = 'KPI Groups'[LEV2_Name]
VAR __vKPI_LEV1_NAME = 'KPI Groups'[LEV1_Name]
//Get additional attributes
VAR __vGetTimePeriod = SELECTEDVALUE ( 'Time Period Selector'[Period])
VAR __vGetKPI_Formula = LASTNONBLANK(SELECTCOLUMNS(RELATEDTABLE('KPI Groups Details'),"Formula",[Formula]),TRUE())
VAR __vGetDataType = SELECTEDVALUE('Data Type'[Data Type])
//Filtering conditions
VAR __vFilterCondCATDefault = {"a","b","c"}
VAR __vFilterCondCATINPL = "z"
VAR __vFilterCondCATINAC = "x"
VAR __vFilterCondCATFT_PL = "y"
VAR __vFilterIA = FILTER(VALUES('ID'[DEP]),NOT('ID'[DEP]=BLANK()))
VAR __vFilterID = FILTER(VALUES('ID'[DEP]),'ID'[DEP]=BLANK())
VAR __vFilterOU = FILTER(VALUES('OA'[NAME]),NOT([NAME] IN {BLANK(),"INF"}))
VAR __vFilterCATDefault = FILTER(VALUES('Category'[Category]),[Category] IN __vFilterCondCATDefault)
VAR __vFilterCATINPL = FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATINPL)
VAR __vFilterCATINAC = FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATINAC)
VAR __vFilterCATFT_PL = FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATFT_PL)
VAR __vFilterACCFT_MTD = FILTER(VALUES('ACC'[ACC]),NOT(CONTAINSSTRING('ACC'[ACC],"AVG")))
VAR __vFilterACCFT_YTD = FILTER(VALUES('ACC'[ACC]), CONTAINSSTRING('ACC'[ACC],"AVG"))
//Calculate KPI Groups
VAR __vCalculateIN_AC = CALCULATE ([__mSwitchSign],__vFilterCATINAC,__vFilterIN)
VAR __vCalculateIN_PL = CALCULATE ([__mSwitchSign],__vFilterCATINPL,__vFilterIN)
VAR __vCalculateIN = IF(__vGetDataType IN {"PL"},__vCalculateIN_PL,__vCalculateIN_AC)
VAR __vCalculateFT_MTD = CALCULATE(-[__mSwitchSign],__vFilterACCFT_MTD,__vFilterCATDefault )
VAR __vCalculateFT_YTD = CALCULATE(-[__mSwitchSign],__vFilterACCFT_YTD,__vFilterCATDefault)
VAR __vCalculateFT = IF(__vGetTimePeriod = "MTD",__vCalculateFT_MTD,__vCalculateFT_YTD)
//Switch Conditions for KPI Groups
VAR __vSwitchOnINGroups_MN = __vKPI_LEV1_NAME="AAA" && __vKPI_LEV2_NAME="CCCCC" && __vGetDataType IN {"AC","PL"}
VAR __vSwitchOnINGroups_TG = __vKPI_LEV1_NAME="AA" && __vKPI_LEV2_NAME="BBB" && AND(__vKPI_ID>=380,__vKPI_ID<=398) && __vGetDataType IN {"AC","PL"}
VAR __vSwitchOnFT = CONTAINSSTRING(__vKPI_LEV3_NAME,"FT")
VAR __vSwitchOnFT_IN = CONTAINSSTRING(__vKPI_LEV3_NAME,"FT*AD") || CONTAINSSTRING(__vKPI_LEV3_NAME,"FT*INT")
VAR __vSwitchOnSignChange = CONTAINSSTRING(__vGetKPI_Formula,"-*{C,") && NOT(CONTAINSSTRING(__vGetKPI_Formula,"+")) && LEFT(__vGetKPI_Formula,1)="-"
//Switch Conditions for KPI Formulas
VAR __vSwitchOn_NRPS = __vKPI_LEV3_NAME="Net Revenue Professional Services"
VAR __vSwitchOn_SoftNR = __vKPI_LEV3_NAME="Software NR of Total NR"
VAR __vSwitchOn_ProdRATE = __vKPI_LEV3_NAME="Production Rate"
VAR __vSwitchOn_UtilRATE = __vKPI_LEV3_NAME="Utilization Rate"
VAR __vSwitchOn_NRPH = __vKPI_LEV3_NAME="Net Rate per Hour"
VAR __vSwitchOn_SalCFS = __vKPI_LEV3_NAME="Salary of CFS FT p.m."
VAR __vSwitchOn_EBIT_Margin = __vKPI_LEV3_NAME="EBIT margin % (pre investments)"
VAR __vSwitchOn_EBITImpact = __vKPI_LEV3_NAME="Investments (EBIT impact)"
VAR __vSwitchOn_EBITinv = __vKPI_LEV3_NAME="EBIT incl. Investments"
//Calculate KPI's
// [Net Revenue Professional Services] = [Net Revenues] - [Net Revenue Licenses, Revenue Licenses] - [Net Revenue Recurring Software, Revenue Recurring Software] - [Net Revenue Managed Services, Revenue Managed Services]
VAR __vCalculateKPI_NRPS = CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Revenues")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Licenses, Revenue Licenses")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Recurring Software, Revenue Recurring Software")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Managed Services, Revenue Managed Services")
// [Production Rate] = [Net Available Hours - Professionals] / [Available Hours]
VAR __vCalculateKPI_ProdRATE = DIVIDE(CALCULATE(-[__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Available Hours - Professionals")
, CALCULATE(-[__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Available Hours"))
// [Utilization Rate] = [Total Chargeable Hours] / [Net Available Hours - Professionals]
VAR __vCalculateKPI_UtilRATE = DIVIDE(CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Total Chargeable Hours")
, CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Available Hours - Professionals"))
//Default calculation
VAR __vCalculateDefault = CALCULATE([__mSwitchSign],__vFilterIndustry,__vFilterOU,__vFilterCATDefault)
RETURN
SWITCH(TRUE(),
// Switch for IN Calculation
__vSwitchOnINGroups_MN ,__vCalculateIN,
__vSwitchOnINGroups_TG ,__vCalculateIN,
// Switch for FT Calculation
__vSwitchOnFT ,__vCalculateFT,
// Switch for KPI Formulas Calculation
__vSwitchOn_NRPS ,__vCalculateKPI_NRPS,
__vSwitchOn_ProdRATE ,__vCalculateKPI_ProdRATE,
__vSwitchOn_UtilRATE ,__vCalculateKPI_UtilRATE,
__vSwitchOn_NRPH ,__vCalculateKPI_NRPH,
// Change sign for special KPI's
__vSwitchOnSignChange ,-__vCalculateDefault,
// If no other conditions then return default calculation
__vCalculateDefault
)
)
RETURN
SWITCH (
TRUE (),
// Remove calc for levels 1 and 2
ISINSCOPE ( 'KPI Groups'[LEV3_Name] ) || ISFILTERED ( 'KPI Groups'[LEV3_Name] ), __vReturn,
ISINSCOPE ( 'KPI Groups'[LEV2_Name] ) , BLANK(),
ISINSCOPE ( 'KPI Groups'[LEV1_Name] ) , BLANK(),
BLANK()
)
@Anonymous , I think you should consider calculation groups or field parameters
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Field parameter - https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |