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() )
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! ThanksPerformance Issue with AVERAGEX()
Hi community I have a scenario where I'd like to calculate a % of reached duration. Let's assume I have to Contracts A & B with a given contract duration ("Contract Duration"). The report user is now able to select a date from a slicer ("UserSelection Date"). Depending on this UserSelection Date I'd like to know what percentage of the "Contract Duration" was achieved. Product A: Because the date difference of the "Contract End" and "UserSelection Date" is only 1 day, 99.86% was achieved yet. Product B: Because the "Contract End" is before "UserSelection Date", already 100% were achieved. When there would be a product with a "Contract Start" after "UserSelection Date", the % of duration reached would be 0%. The Total of both products is nearly 100%. When the user changes the "UserSelection Date" to 14.09.2022 or later, the Total is 100%: Now the calculation of the single rows is simple ("% of Duration reached Step 1"--> Date Diff UserSelection / Contract Duration) But the calcuation of the Total differs, because you cannot take the sums of this two values. That's why I'm calculating "% of Duration reached Step 2" with the AVERAGEX of the "% of Duration reached Step 1". This will return the correct value, but is extremly slow. I have only around 2 Million rows and I'm using the import mode - but the calculation of the Step 2 takes around 1 Minute. Additional information: "Product" is just one sample dimension, the formulas should work for other dimensions as well (i.e. vendor). UserSelection Date = MAX(Calendar[Date]) Contract Duration = CALCULATE( SUMX( myTable, DATEDIFF ( myTable[Contract Begin], myTable[Contract End], DAY ) ) ) Date Diff UserSelection = CALCULATE( SUMX( myTable, DATEDIFF ( myTable[Contract Begin], [UserSelection Date], DAY ) ) ) % of Duration reached Step 1 = IF( MAX(myTable[Contract End]) < [UserSelection Date], 1, IF( MAX(myTable[Contract Begin]) > [UserSelection Date], 0, [Date Diff UserSelection] / [Contract Duration] ) ) % of Duration reached Step 2 = AVERAGEX( myTable, [% of Duration reached Step 1] ) Are there other ways to get the correct total value? How can I improve the performance of this calculation? Thank you very much for your support!
Need measures that will work with a larger dataset (out of memory errors)
I have 4 working measures prefixed with # in this example Power BI report. The measures work fine with the example data but when I use real data (approx. 90 million imported rows with filters applied to reduce the dataset for scenario testing) I get out of memory errors on some visuals, unless I apply more filters to reduce the dataset. Is there a way to re-write these measures so that they will work better with a large dataset? Measure 1 is an interim measure to calculate Measure 2: #RT = CALCULATE( SUMX( ADDCOLUMNS( SUMMARIZE( DummyDataID, DummyDataID[ID] ), "ExAmt", [Exceeded Amount] ), [ExAmt] ), FILTER(ALLSELECTED(DummyDataID), DummyDataID[ID] <= MAX(DummyDataDemand[ID])) ) Measure 2 #RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, [#RT] - minValue, [#RT] - minOfSum ) Measure 3 is the sum of the values from Measure 2 #RT with sum = IF(HASONEVALUE(DummyDataID[ID]), [#RT_filtered], SUMX(VALUES(DummyDataID[ID]), [#RT_filtered])) Measure 4 is the highest value #RT max = MAXX( ADDCOLUMNS( SUMMARIZE(DummyDataID, DummyDataID[ID]), "@RT", [#RT with sum] ), [#RT with sum] )
