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() )
Power BI Responsiveness and Performance
Hello, We run a Power BI RS configuration on our domain. This setup was recently updated from the October 2020 build to May 2022. After the update the users have been reporting the following and I wanted to see if there was a way to improve the performance of Power BI. We cannot go to a pbix file and open it. We have to open the application first from the desktop, then use File --> Open. If we try to open a file directly, the PBI icon comes up and it hangs there. It takes several minutes for the PowerBI application to open from desktop. Once a pbix file is open, another one will not open. It starts to open--the PBI icon displays--but it never does open. Similar to bullet 1. This is whether or not we try opening the file directly or use the File menu. Closing a file works fine but we get a message a few minutes after closing that PowerBI has crashed. Power BI RS runs on a Windows Server 2016 VM. The VM has plenty of resources dedicated to it, the memory and CPU are never maxed out.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' ) )
Report renders very slow
Hello, The PBI report takes >30sec to render, this is too slow (using Edge, Chrome is the same slow). Posting the problem in this section as we have on-prem Report Server. After 4-6 sessions with Microsoft, with them obtaining various run logs from us, we are informed unless we have a break to fix problem, Microsoft support cannot help us 😞 The PBI report has the following visuals and Data Connect Mode is Import (about 200 data rows - few seconds to refresh daily ). 1) 1 Bing Map of North America 2) 1 Area chart 3) 4 Slicers and 1 Text Filter 4) 9 Images 5) 18 Advanced Cards (We have the latest May2021 Report Server) The Model in the report is a star-schema. Performance analyzer showed the max DAX query is only 43ms, Visual display and Other are high ones. Current PBI Server is a 4core CPU and max CPU peaked at 58% only (PBI Server, DB and Web all in the same VM). Microsoft said adding CPU would not make a difference. I am trying to trace with the DevTool (F12) in Edge, the 'reportServerHost.js' took 15sec and many processes have long stalled/waiting states. Don't understand what it all means, thinking maybe this can help explain the slow report render. I am hoping the PBI community can help explain or have suggestions on how the report could be improved for render performance. Thank you. Btw, I did another test with a report that just displays 'Hello' (no DB). It took 8sec to load in the browser.
