Multiple KPI Calculations over a KPI Table - options for fast performance
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() )
DAX Measure performance - cumulative totals
I'm still relatively new to DAX and I'm trying to create a cumulative total. I have managed to achieve this, but it's incredibly slow to the point of almost being unusable. I know I'm probably making some obvious errors and the slowness is likely down to some kind of iteration issue, so I would really appreciate any assistance. Here is a sample of the data: Each DATE and Model has a single contract value. Then for each level of the hierarchy there are a series of attributes. Eg. Level 1 - System Attributes - Demand, Retail Level 2 - Product Attributes - Construction, Inernal, Red, Interest, Fundamental, Status and so on. I have created a separate cumulative total for each level of the hierarchy and then used a switch in the chart to change between each one. The cumulative total needs to start from the first value in the date range, and it also needs to respond to any filters applied (e.g. DataSeriesKey in the sample data). Essentially the calculation for Cumulative Total is (pseudo code) (If SUM(Position) >0 THEN 1, If SUM(Position) <1 THEN -1) * Contract Value for that Day/Model Here are the DAX measures I have created, this example shows the System cumulative total: This get's the balance for a particular Date/Model and calculates whether the outcome should be 1 or -1 Derived Balance By System = VAR Position = SUMX ( SUMMARIZE ( FactFiscalSummary, FactFiscalSummary[DATE], FactFiscalSummary[Model], "BalanceDiff", SUM ( FactPnLSummary[PositiveBalances_System] ) - SUM ( FactPnLSummary[NegativeBalances_System]) ), VAR _BalanceDiff = [BalanceDiff] RETURN SWITCH(TRUE(), ISBLANK(_BalanceDiff),BLANK(), _BalanceDiff = 0, 0, _BalanceDiff < 0, -1, _BalanceDiff > 0, 1 ) ) RETURN Position This calculates the max contract value for the day Derived Max Contract Value = VAR ContVal = MAXX ( SUMMARIZE ( FactFiscalSummary, FactFiscalSummary[DATE], FactFiscalSummary[Model], "MaxContractVal", MAX ( FactFiscalSummary[ContractValue]) ), [MaxContractVal] ) RETURN ContVal Finally this calculates the cumulative total: CumulTotal = VAR MaxDate = MAX ( 'DimDate'[Date] ) VAR MinDate = MINX (ALLSELECTED(DimDate),DimDate[Date]) VAR Total = SUMX( CALCULATETABLE( ALLSELECTED(DimDate[Date]), DimDate[Date] <= MaxDate && DimDate[Date] >= MinDate ), FactFiscalSummary[Derived Balance By System] * FactFiscalSummary[Derived Max Contract Value] ) RETURN Total As I said, this works and produces the correct values and behaviour, but it's extremely slow and sometimes takes over a minute to calculate. There are about 16 million rows in FactFiscalSummary and depending on how selective the attributes are, this takes much longer. For example, System only has 2 attributes, Product has 6 attributes and takes much longer, etc. Also I have tried putting all of these into the same measure but I don't get the correct results, I can only achieve this by creating three separate measures. Any pointers would be greatly appreciated! Thanks
Measure is slow when using variables
I have been trying to learn more about Optimising Dax using a data sample from sqlbi (DAX Optimizations Examples with Alberto Ferrari) Orders Table has 1.6 million rows of data. I have two identical measures except one does not use variables. The measure with variables takes 14 seconds to execute and the other 0.5 seconds. I am unsure why variables are having an impact on performance. Measure 1 Open Orders = VAR StartPeriod = MIN ( 'Date'[Date] ) VAR EndPeriod = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= EndPeriod, Orders[Delivery Date] >= StartPeriod ) ) ), ALL ( 'Date' ) ) Measure 2 Open Orders = CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= MAX ( 'Date'[Date] ), Orders[Delivery Date] >= MIN ( 'Date'[Date] ) ) ) ), ALL ( 'Date' ) )
Slow Dax Measure is there anyway to increase the performance
Hi guys, I have the measure: var _measure1 = Measure1 var _measure2 = Measure2 var _measure3 = Measure3 Return IF (_Measure1 > _Measure2, 2, IF( _Measure1 > _Measure3, 1, 0 )) I have a performance problem here, and I already tried the calculate with filter, but the problem is, that _Measure2 and _Measure3 are created by other multiples measures, and the filter in the CALCULATE function is just for one column. Does someone have one suggestion about this? other measure is: var _measure4 = Measure4 var _measure5 = Measure5 Return IF (_Measure4 - _Measure5 < 0, 0, _Measure4 - _Measure5 ) The same here, the _Measure4 and _Measure5 are created by other measures.Performance issues with SUMX
Hi, everyone, I'm working on a dax measure performance, this measure contains as funtion SUMX (As SUMX is an iterator function, it is degrading the performance) and I woul like to use another alternative to fix the performance issue. the measure that I'm working on : Measure : SUMX(FILTER(VALUES(TABLE1[Column A]),[TAG]<>BLANK()),CALCULATE(DISTINCTCOUNT(TABLE1[Column A]))) Thank you for your response. My best regards.
