optimize

7 Topics

Questions for optimization and DAX expression
There is one customer column as CUST_CODE which we want to create a calculated column which has the customer value which is filtered based on the REVENUE column. Basically we want just those customers which has revenue. So want to create a calculated column. I am using this formula and getting the error. =CALCULATE(VALUES(FACT_SALES[CUST_CODE]),[Revenue]<>blank())
Adwitiy_4209211
3 years ago Place DAX Commands and Tips
761Views
0likes
3Comments
Optimize SUMX / SUMMARIZE Measure
Hi everyone, I'm writting here to get some help to optimize the calculation of one of my measure that takes too much time to compute (10 seconds everytime you click on something / expand a row) Let me explain the report : Model : I've managed to get a Star Schema with a Fact Table based with two base measure : Turnover and CountArticle The date granularity is Quarter - Year : it is already aggregated. I have many dimensions but in this example, I only use 4 : Date Seller Store : Sells an article Article : Description of an article Customer : Buys an article. It has a dedicated SalesMan and SalesTeam Model Size : About 2 000 000 records in Fact Table. Dimension are way smaller. (Except Customer : I've loaded the entire dimension : Maybe not optimized) Subject : Sales on article that are sold by my company. Slicers : Year Quarter IsLogistic (feature of an article : used to exclude / include logistic products) Visual Matrix Visual Hierarchy Sales Team Sales Man Customer Seller Store Article Values SumTurnover = SUM ( Fact[Turnover] ) SumTurnoverLastYear = CALCULATE ( SUM ( Fact[Turnover] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) - 1 && 'Date'[Quarter] = MAX ( 'Date'[Quarter] ) ) ) SumCountArticle= SUM ( Fact[CountArticle] ) SumCountArticleLastYear = CALCULATE ( SUM ( Fact[CountArticle] ), FILTER ( ALL ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) - 1 && 'Date'[Quarter] = MAX ( 'Date'[Quarter] ) ) ) PriceImpact = IF ( SumTurnover = 0 || SumTurnoverLastYear = 0 || SumCountTest = 0 || SumCountTestLastYear = 0 || ISBLANK ( SumTurnover ) || ISBLANK ( SumTurnoverLastYear ) || ISBLANK ( SumCountTest ) || ISBLANK ( SumCountTestLastYear ), BLANK (), ( DIVIDE ( SumTurnover, SumCountTest ) - DIVIDE ( SumTurnoverLastYear, SumCountTestLastYear ) ) * SumCountTestLastYear ) And this is where it became truly annoying. I want the Price Impact to be calculated at the Article level, then sum up to get the value at the higher level of the hierarchy. Hierarchy Values Seller Store Article Turnover TurnoverLastYear CountTest CountTestLastYear Price Impact SS1 A 500 600 2 3 150 SS1 B 200 300 3 3 -100 SS1 C 300 200 4 4 100 SS2 A 600 600 2 1 -300 SS2 B 200 200 3 3 0 Rollup to Seller Store Level (With +/- buttons ) : I want the Price Impact of the article level to be summed up not recalculated Hierarchy Values OK (SUM) KO (Recalculated) Seller Store Turnover TurnoverLastYear CountTest CountTestLastYear Price Impact Price Impact2 SS1 1000 1100 9 10 150 166,6666667 SS2 800 800 5 4 -300 12000 I hope it is clear. To get this result and because I have (as described before) several level in the hierarchy : I've used the following measure in my Matrix with SUMX and SUM. Even if I still do not fully understand under the hood, it is working as expected and giving me the right result. SummedPriceImpact = SUMX ( SUMMARIZE ( 'Fact', 'Customer'[Sales Team], 'Customer'[Sales Man], 'Customer'[Customer], 'Store'[Store], 'Article'[Article] ), [PriceImpact] ) BUT It takes quite a long time to compute. And it is not acceptable for my customers. I have some thoughts on my measures : Usage of var instead of repeating measure Optimizing the If Statement Optimizing the final measure with Summarize. I know that Summarize is deprecated and should be replaced by SummarizeColumn but I haven't been able to make the latest function work in my report (because of filter context) It would be great to work together to find a suitable solution for my end users to reduce the calculation time. (I haven't been able to share a .pbix : I'm on vacation without access to Power BI (PC at work , MAC at home.. ) ) Thank you in advance for your help. 🙂 Romain
Solved
Anonymous
4 years ago Place DAX Commands and Tips
15KViews
0likes
9Comments
Direct Query optimization with date table
Hi everyone, I have a simplified model with a calender table [BLAllgemein Datum] and a Fact Table [BL_Vertrag FAKT ...] looking like this. The fact table has - 1 summable column "AnzahlLaufenderVertrag" and - 2 date columns [StatistischGueltigAbDatum] and [StatistischGueltigBisDatum] which indicate, in which date range the particular row is valid. The calender table includes the dates and some transformations on them, nothing special. All I want to do is to calculate and display this measure __AnzVerträgeBestand, which will calculate the sum of [AnzahlLaufenderVertrag] for each date in the calender table, under the condition that the date range in the fact table includes this date. __AnzVerträgeBestand = VAR _Date = MAX ( 'BLAllgemein Datum'[Datum] ) VAR _result = CALCULATE ( SUM ( 'BL_VERTRAG FAKT_BESTAND Table'[AnzahlLaufenderVertrag] ), 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigabDatum] <= _Date, 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigbisDatum] > _Date, REMOVEFILTERS ( 'BLAllgemein Datum' ) ) RETURN _result Both tables are located in an MS SQL database and I try to query them in direct query mode. Unfortunately, the query is very slow taking about 14 seconds on my small test fact table with about 100.000 rows. In production, there are about 20 million rows, and the query runs several minutes. I've done some research and noticed, that Power BI generates an extremely long an inefficient SQL query that looks like this: // Direct Query SELECT TOP (1000001) [semijoin1].[c1],[basetable0].[c43],SUM( CAST([a0] as BIGINT) ) AS [a0] FROM ( ( SELECT [t3].[StatistischGueltigabDatum] AS [c43],[t3].[StatistischGueltigbisDatum] AS [c44],[t3].[AnzahlLaufenderVertrag] AS [a0] FROM ( ( select [AnzahlLaufenderVertrag], [StatistischGueltigabDatum], [StatistischGueltigbisDatum] from [BL_VERTRAG].[FAKT_BESTAND] as [$Table] ) ) AS [t3] WHERE ( ([t3].[StatistischGueltigabDatum] IN (CAST( '20150205 00:00:00' AS datetime),CAST( '20140428 00:00:00' AS datetime),CAST( '20131111 00:00:00' AS datetime),CAST( '20140331 00:00:00' AS datetime),CAST( '20201001 00:00:00' AS datetime),CAST( '20080728 00:00:00' AS datetime),CAST( '20060401 00:00:00' AS datetime),CAST( '20191004 00:00:00' AS datetime),CAST( '20190601 00:00:00' AS datetime),CAST( '20150325 00:00:00' AS datetime),CAST( '20181116 00:00:00' AS datetime),CAST( '20160803 00:00:00' AS datetime) ,CAST( '20110908 00:00:00' AS datetime),CAST( '20100804 00:00:00' AS datetime),CAST( '20190410 00:00:00' AS datetime),CAST( '20210119 00:00:00' AS datetime),CAST( '20161027 00:00:00' AS datetime),CAST( '20180612 00:00:00' AS datetime),CAST( '20120201 00:00:00' AS datetime),CAST( '20120711 00:00:00' AS datetime),CAST( '20150616 00:00:00' AS datetime),CAST( '20140901 00:00:00' AS datetime),CAST( '20120301 00:00:00' AS datetime),CAST( '20150323 00:00:00' AS datetime),CAST( '20150820 00:00:00' AS datetime) ,CAST( '20090306 00:00:00' AS datetime),CAST( '20141223 00:00:00' AS datetime),CAST( '20141212 00:00:00' AS datetime),CAST( '20200328 00:00:00' AS datetime),CAST( '20090610 00:00:00' AS datetime),CAST( '20130820 00:00:00' AS datetime),CAST( '20200701 00:00:00' AS datetime),CAST( '20150411 00:00:00' AS datetime),CAST( '20110214 00:00:00' AS datetime),CAST( '20080703 00:00:00' AS datetime),CAST( '20210304 00:00:00' AS datetime),CAST( '20140602 00:00:00' AS datetime),CAST( '20191021 00:00:00' AS datetime) ,CAST( '20101001 00:00:00' AS datetime),CAST( '20190904 00:00:00' AS datetime),CAST( '20110610 00:00:00' AS datetime),CAST( '20120404 00:00:00' AS datetime),CAST( '20170418 00:00:00' AS datetime),CAST( '20060207 00:00:00' AS datetime),CAST( '20150422 00:00:00' AS datetime),CAST( '20191119 00:00:00' AS datetime),CAST( '20160905 00:00:00' AS datetime),CAST( '20190710 00:00:00' AS datetime),CAST( '20150930 00:00:00' AS datetime),CAST( '20210301 00:00:00' AS datetime),CAST( '20181203 00:00:00' AS datetime) ,CAST( '20190502 00:00:00' AS datetime),CAST( '20190312 00:00:00' AS datetime),CAST( '20100308 00:00:00' AS datetime),CAST( '20181115 00:00:00' AS datetime),CAST( '20080620 00:00:00' AS datetime),CAST( '20171012 00:00:00' AS datetime),CAST( '20170602 00:00:00' AS datetime),CAST( '20170111 00:00:00' AS datetime),CAST( '20170621 00:00:00' AS datetime),CAST( '20191017 00:00:00' AS datetime),CAST( '20120809 00:00:00' AS datetime),CAST( '20150708 00:00:00' AS datetime),CAST( '20150226 00:00:00' AS datetime) ,CAST( '20181213 00:00:00' AS datetime),CAST( '20200601 00:00:00' AS datetime),CAST( '20090615 00:00:00' AS datetime),CAST( '20150922 00:00:00' AS datetime),CAST( '20060901 00:00:00' AS datetime),CAST( '20160726 00:00:00' AS datetime),CAST( '20171209 00:00:00' AS datetime),CAST( '20140923 00:00:00' AS datetime),CAST( '20120703 00:00:00' AS datetime),CAST( '20190101 00:00:00' AS datetime),CAST( '20190409 00:00:00' AS datetime),CAST( '20100112 00:00:00' AS datetime),CAST( '20190515 00:00:00' AS datetime) ,CAST( '20170316 00:00:00' AS datetime),CAST( '20190902 00:00:00' AS datetime),CAST( '20160503 00:00:00' AS datetime),CAST( '20180524 00:00:00' AS datetime),CAST( '20101103 00:00:00' AS datetime),CAST( '20150910 00:00:00' AS datetime),CAST( '20121116 00:00:00' AS datetime),CAST( '20170201 00:00:00' AS datetime),CAST( '20131024 00:00:00' AS datetime),CAST( '20180111 00:00:00' AS datetime),CAST( '20210128 00:00:00' AS datetime),CAST( '20090304 00:00:00' AS datetime),CAST( '20180120 00:00:00' AS datetime) ,CAST( '20180829 00:00:00' AS datetime),CAST( '20180314 00:00:00' AS datetime),CAST( '20130701 00:00:00' AS datetime),CAST( '20080616 00:00:00' AS datetime),CAST( '20190201 00:00:00' AS datetime),CAST( '20170303 00:00:00' AS datetime),CAST( '20180717 00:00:00' AS datetime),CAST( '20110727 00:00:00' AS datetime),CAST( '20120725 00:00:00' AS datetime),CAST( '20130228 00:00:00' AS datetime),CAST( '20151015 00:00:00' AS datetime),CAST( '20161031 00:00:00' AS datetime),CAST( '20171001 00:00:00' AS datetime) ,CAST( '20201007 00:00:00' AS datetime),CAST( '20160707 00:00:00' AS datetime),CAST( '20170815 00:00:00' AS datetime),CAST( '20200203 00:00:00' AS datetime))) ) ) AS [basetable0] INNER JOIN ( (SELECT 44198 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44199 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44200 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44201 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44202 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44203 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL .... (SELECT 44560 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44561 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44562 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) )) AS [semijoin1] on ( ([semijoin1].[c44] = [basetable0].[c44]) ) ) GROUP BY [semijoin1].[c1],[basetable0].[c43] This is already very long, but in fact the query has about 7.000 rows with tons of Casts of datetime values. On the other hand, I have recreated the query in T-SQL myself, coming up with this solution: SELECT TOP (1000001) [t4].[Datum], SUM(CAST([t5].[AnzahlLaufenderVertrag] as BIGINT)) AS [a0] FROM (( select SUM([AnzahlLaufenderVertrag]) as AnzahlLaufenderVertrag, [StatistischGueltigabDatum], [StatistischGueltigbisDatum] from [RADaR_Sandbox].[BL_VERTRAG].[Fakt_Bestand] as [$Table] group by [StatistischGueltigabDatum], [StatistischGueltigbisDatum] ) AS [t5] LEFT OUTER JOIN ( select [$Table].[Datum] as [Datum], [$Table].[JahrMonat] as [JahrMonat] from [BLAllgemein].[Datum] as [$Table] WHERE Jahr = 2021 ) AS [t4] on ( [t5].[StatistischGueltigabDatum] <= [t4].[Datum] and [t5].[StatistischGueltigbisDatum] > [t4].[Datum] ) ) GROUP BY [t4].[Datum] order by [t4].[Datum] The latter query takes only a fraction of a second and produces exactly the same result as the long query generated by Power BI. Can I do anything (in the dax query, the data model or direclty in the database) so that PowerBI will find a more efficient way to calculate the desired result? Any help is appreciated!
IMett
4 years ago Place DAX Commands and Tips
1.1KViews
0likes
2Comments
Rewrite Filter function with better option (Optimize Dax Measure)
Hi Guys! I need your help please, I have a model and I need to calculate a Sum of a column as a measure, the measure works fine, shows me the correct number, but when I begin to use other dimensions, the time expended for the visual that use this metric begin to increase (the visual has only that measure), so I was analyzing the measure, and I find the filter that makes the visual slower (is a filter in the metric), it's highlighted in another image, so I want to replace that filter, but I don't know-how, can you give me any advice? Below you can find the measure, the model and the current results of a visualization (that are correct).
Solved
Anonymous
6 years ago Place DAX Commands and Tips
2.5KViews
0likes
10Comments
IF function optmization
Hello! I have a measure currently that compares a customer's "first sale date" (date of their first purchase) against my current calendar table date selections. My problem is that the DAX is taking well over 200,000ms to compute. Data Model Structure The data model I'm using has quite a bit of relationships and tables included, but I'll confine this to just the relevant tables. Tables: dim_cust (customer table) fct_cust_date (daily customer sales) vw_dim_dates (view of our date table with a "Current Date" column added to circumvent UTC time shenanigans) Relationships: dim_cust (CustID) to fct_cust_date (CustID) fct_cust_date (dateid) to vw_dim_dates (dateid) Reporting Need In my current report, I have date slicers that users can select from including Year and Month. I have a calculated column for each determining the "Current Year" and the "Current Month" so that those can be a dynamic default selection. What I'd like to be able to do, is have a visual that displays whether a customer's "first sale date" is within the dates that have been selected in the Year and Month slicers. I can get this to work, but the performance has been terrible and the visual is nearly unusable. DAX My current measure is as follows: FS Date in Selection = VAR MaxDateSelected = MAX( vw_dim_dates[date1] ) VAR MinDateSelected = MIN( vw_dim_dates[date1] ) VAR FSDate = MAX( dim_cust[dim_dates.date1] ) RETURN IF( AND( FSDate <= MaxDateSelected , FSDate >= MinDateSelected ) , TRUE() , FALSE() ) I think the main issue is that my dim_cust table is not directly connected to my vw_dim_dates table through the customer's first sale date, but this would introduce another issue. A relationship like that would limit me from displaying customers whose first sale date is outside the selected date range. Any thoughts on how I can increase the efficiency of my current measure while still meeting the reporting needs? Thank you!
Anonymous
6 years ago Place DAX Commands and Tips
2KViews
0likes
4Comments
DAX optimisation row-wise calculation
Hello guys, need some help. I have to calculate listed employee number - its average employee number. I have calculated two measures: Employee count = VAR total = CALCULATE( DISTINCTCOUNT(Employee[No_]); FILTER(ALL('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP('Calendar'[Date];Employee[Data_Joined])) VAR fired = CALCULATE( COUNTA(Employee[Data_Left]); FILTER(ALL('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP(Employee[Data_Joined];'Calendar'[Date])) RETURN IFERROR(total-fired;BLANK()) Listed employee = AVERAGEX('Calendar';Employee[Employee count]) The calculation is ok, but it took quite a long time with uploading to the table. With new Performance Analyze it shows that it took 79096 ms for the table to upload Please, can anyone help me to improve performance? Pbix file (I can't make active relationships because of additional tables that I use in production) https://drive.google.com/open?id=17AXwVhnTcrHZNjZUsDensXxsQ8VusJRz
Anonymous
7 years ago Place DAX Commands and Tips
1.5KViews
0likes
2Comments
Help with Power BI query optimization
Hi All, I need your help to optimize this power BI query. Currently, the query works very slow (query doesnt return result even after 3 days). let Source = Sql.Database("chvpkw8ahsv117", "hmparsdmd"), Staging_Transactions_Summary = Source{[Schema="Staging",Item="Transactions_Summary"]}[Data], #"Removed Other Columns" = Table.SelectColumns(Staging_Transactions_Summary,{"TransactionDate", "TransactionYYMM", "LocationID", "CardTypeDesc", "FuelTypeDesc", "td_acct_no", "PumpNumber", "IndoorOutDoor", "NFCUsed", "PilotSites", "TransactionCount", "Gallons", "FuelDollars"}), #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([IndoorOutDoor] = "Outdoor") and ([PilotSites] = "Yes") and ([NFCUsed] = "No")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"LocationID", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"LocationID", Order.Ascending},{"td_acct_no", Order.Ascending},{"TransactionDate", Order.Ascending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"LocationID", "td_acct_no", "TransactionDate", "CardTypeDesc", "FuelTypeDesc", "PumpNumber", "IndoorOutDoor", "NFCUsed", "PilotSites", "TransactionCount", "Gallons", "FuelDollars"}), #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"LocationID", "td_acct_no","Index"},#"Staging Transactions_Summary",{"LocationID", "td_acct_no","Index"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"TransactionDate"}, {"NewColumn.TransactionDate"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.TransactionDate", "NextTransactionDate"}}), #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([NextTransactionDate] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "DaysFromLastTrans", each if [NextTransactionDate] <> null then Duration.Days(Duration.From([NextTransactionDate]-[TransactionDate])) else null), #"Grouped Rows" = http://itopssqldb.database.windows.net(#"Added Custom", {"LocationID", "TransactionYYMM", "td_acct_no", "CardTypeDesc", "FuelTypeDesc", "IndoorOutDoor", "NFCUsed"}, {{"TransactionCount", each Table.RowCount(_), type number}, {"TotalGallons", each List.Sum([Gallons]), type number}, {"TotalFuelDollars", each List.Sum([FuelDollars]), type number}, {"TotalDays", each List.Sum([DaysFromLastTrans]), type number}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "AvgDaysBetweenTrans", each [TotalDays]/[TransactionCount]) in #"Added Custom1"
Solved
paswani
8 years ago Place Desktop
7.9KViews
0likes
4Comments