Performance Issues with Embedded Power BI Dashboards
I have embedded multiple Power BI dashboards on my website. However, when multiple end users try to access the dashboards simultaneously, they experience significant delays in loading. I have verified that the dashboard performance is generally fast, and I have implemented incremental refresh, RLS methods, and have only 4 to 5 visuals with several filters. I am using Pro licenses.
Query optimization assistance
Hello, I am trying to see if there is a way to optimize the measure in the sample attachment. The model details an asset (automobile) with events (EventFacts) on a given date. Each automobile may or may not be associated with a configuration (AssetEventMapping). What I would like is a distinct count of assets by day (see DateTable) only if the asset has a configuration associate in the AssetEventMapping table. The model details below show example tables & records with the desired result. As can be seen, assets 6 and 7 do not show up in daily count because they do not contain a record in the mapping table (filtered out via the "in distinct" portion of hte measure). This measure works fine with small dataset, but my real dataset contains millions of records and this query is taking ~5 seconds. Thanks Tables: Measure: Count of Assets Reporting = calculate( DISTINCTCOUNT(EventFacts[AssetId]), FILTER( EventFacts, EventFacts[AssetID] in (distinct(AssetEventMapping[AssetID])) ) )
Calculation group performance issue
Hello everyone, I face an issue about performance when I use calculation groups in my model. To give some context items, my dataset is quite complex (around 40 tables) and is stored on a premium capacity P2. It's a composite model that is using the user defined agregation feature of Power BI so we manage a detailed fact table and an aggregated one. To simplify my case, please consider the example below : - I have one measure "Amount" (sum of my fact table) - I have one measure "Amount Last Year" - I have a calculation group with one item that calculate Last Year value for the selected KPI - Then, I create two matrix on my report : - One is using the measure "Amount Last Year" to display last year amount per fiscal period - One is using the measure "Amount" on which I apply the calculation item "Last Year" to display last year amount per fiscal period When I compare the performance of the two visuals, I face a very big gap in term of performance. This example is a lot more simplified than the visual i'm creating for my reporting purpose. In real situation, visuals are taking to much time when I use calculation groups so it's not possible to use them. Does someone have any idea about where this performance gap could come from ? Thanks, R_LBThe most efficient way to perform lookup in table
Hello guys, how are you? I'm having a very specific issue, and even knowing how to solve that in theory, I'm having serious performance problems, due to the obligatory use from a very heavy database - an azure cube developed by my company that provides data worldwide. I've come with an example in a fact table, where I have trips from loading vehicles with an initial weight in the beggining of the trip. I need to create a measurement (can't make new columns in the direct query) that calculates the initial weight for the next trip from this same vehicle. It's a very simple task with ALL filters and on, however the real table is not retrieving the data due to the mentioned size of the datamodel. I've been trying using the OFFSET funcion, but I'm not being able to make the logic work to my problem. Any suggestions? Below, the latest try I've made: Next trip weight = VAR vFrota = MAX('Shifts and Events'[VehicleUsedId]) VAR vData = MAX('Shifts and Events'[EventStartDateTime]) VAR vTab = FILTER( ALL('Shifts and Events'), 'Shifts and Events'[EventStartDateTime] > vData && 'Shifts and Events'[VehicleUsedId] = vFrota ) VAR vMenorData = CALCULATE( MIN('Shifts and Events'[EventStartDateTime]), vTab ) RETURN CALCULATE( MAX('Shifts and Events'[MeasureBeforeLoading(kg)]), FILTER( vTab, [EventStartDateTime] = vMenorData && [VehicleUsedId] = vFrota ) ) Below an example of how I'm trying to make the information appear: Thanks!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() )
