Optimize/Improve DAX Performance on AVG calculation
I have a data model with the following tables: 'Date' (well formed) 'Projects' (dimension) - a list of all projects 'Project Status' (dimension) - status a project can have, Created (1), Opened (2), Closed (3) 'Months Projects Open' (fact) - one row for each month a project is open 'Months Projects Open' Project.ID Months Project Status 545 2025/01/01 Created 545 2025/02/01 Open 545 2025/03/01 Closed The Months column is always the first of the month as the whole table is generated in Power Query from other data sources and we only care about granularity to the month level. I've written a measure to return the annual average number of projects (count rows) in the row context so that I can compare the current month to the annual average. I can also slice by the Project Status so I can look at average Created & Closed. However the measure is 'slow' relatively speaking compared to all the other measures I've written. I think my DAX is just not optimized very well, and I suspect I may not be handling my context transitions as elegantly as I could. Assuming I can improve performance I also need to do some logical handling on the 'Open' state, when it is sliced by Status, so that the Created and Closed months are included with 'Open' that is if I count only 'open' rows, I should include Created and Closed (if present). I would love any advice on how to improve the performance of this code. Also, here is a sampe of the results in a matrix. Yrly Avg Proj Per Month = VAR _dateStart = STARTOFYEAR('Months Projects Open'[Months]) VAR _dateEnd = ENDOFYEAR('Months Projects Open'[Months]) VAR _selectedYears = ALLSELECTED('Date'[Fiscal Year]) VAR _validMonths = COUNTROWS( CALCULATETABLE( SUMMARIZE( 'Date', 'Date'[Year Month Number] ), DATESBETWEEN('Date'[Date], _dateStart, _dateEnd) ) ) VAR _projects = CALCULATE( [Count v2], REMOVEFILTERS('Date'[Year Month Number]), _selectedYears, 'Date'[Date] >= _dateStart, 'Date'[Date] <= _dateEnd ) VAR _result = DIVIDE( _projects, _validMonths ) RETURN _result
Calculation Groups Very Slow
I am working on a report that uses calculation groups to let users select which time intelligence groups they would like to see (CY YTD, PY PTD, YTD Variance, CY TTM, PY TTM, TTM Variance). There is also a perspective set up to allow them to pull in certain dimensions from the dataset. This is all set up in Tabular Editor opened from the dataset. The KPIs are DAX measures within the Power BI Report itself. The dataset is a star schema and has several million records. Does anyone have any advice for how to optimize and speed up calculation groups within Power BI? Could it be related to my DAX measures being in the report instead of the DS? Any advice or things to try would be helpful. Thanks!
DISTINCTCOUNT Optimization
I believe the DistinctOrders DAX measure below gives accurate results and is faster than other versions I've tried but I need to know if I can make it faster. With the model I'm working with (see below) this takes around 12 seconds in DAX Studio server timings when slicing by products in ProdDim (which is unfortunately snowflaked to the fact table through a very large dim table). I'm hoping for something less than 3 seconds. Maybe 12 seconds is the best possible result given my data size and model structure but just want to make sure. Please assume I'm stuck with the model size and structure. Model ProdDim (225,000 rows)--->TransactionDim (5 million rows 😧)--->SalesFact (110 million rows) Calendar[Date]--->SalesFact[DeliveryDate] (active relationship) or SalesFact[OrderDate] (inactive relationship) Measures OrderTransactionQuantity by Order Date = CALCULATE ( SUM ( 'SalesFact'[OrderTransactionQuantity] ), USERELATIONSHIP ( 'SalesFact'[Order Date], 'Calendar'[Date] ) ) DistinctOrders = CALCULATE ( DISTINCTCOUNT ( 'SalesFact'[ClientID] ), KEEPFILTERS ( FILTER ( ALL ( 'SalesFact'[ClientID] ), [OrderTransactionQuantity by Order Date] <> 0 ) ), USERELATIONSHIP ( 'SalesFact'[Order Date], 'Calendar'[Date]) ) Example SalesFact Data: ClientID TranDimKey Order Date Delivery Date OrderTransactionQuantity 1 HTX180 2022-01-05 2022-01-07 1 1 HTX180 2022-01-05 2022-01-08 -1 1 BTX260 2022-01-09 2022-01-11 1 2 BTX270 2022-01-12 2022-01-14 1 3 BTX390 2022-01-15 2022-01-16 1 3 BTX380 2022-01-16 2022-01-17 1 4 HTX170 2022-01-16 2022-01-17 1 4 HTX170 2022-01-16 2022-01-19 -1optimisation of mobile median in dax
Hello, I would like to optimise the calculation below. I guess if function or switch function would be helpfull. Do you have any recommandation ? MobileMedian3period= = ( calculate( MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=1)) + calculate(MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=2)) + calculate(MEDIAN(waitingtable[nNb_days_waiting_total]), filter(table_year_period, table_year_period[no_periode(row)]=3)) )/3
Include only articles that have moved consecutively during selected months
Hi everyone, This is my first post here, as I have only recently started working with PBI (I'm coming from a Qlik world, but that might change). With that in mind, I hope that I'm able to formulate my needs as clear as possible. So, I have a problem that I've been stuck for quite some while now. Intro I want to analyze the average sales price development on a monthly basis. The requirements are 1) Calculate the average price per company, article, and year-month; then sum these values up (it's specific to our situation); 2) Only analyze articles that have moved consecutively during the time period that is selected by the user, i.e. if I select year 2021 and months jan-sept, I need to dynamically include only articles that have moved at least once per month during 2021 jan-sept. Month measures For this I first deciced to calculate how many months are available in total (with a minor tweak to remove the current month under certain conditions), a number that should depend only on the time selection: Then, I calculate the months that are available, accepting all filters: Lastly, I create a new measure that is a straight comparison between above two measure, i.e. a boolean that I mean to use as a filter later: So far so good, in as much as these measures verifiably work. Article filter Now comes the difficult part: creating a filter of articles where this latter condition is true. I have tried many things that don't work, so this is what I have at the moment: This works - however, it is extremely slow. As you may have already realized, the FILTER(...) with my boolean filter condition is the culprit. The measure takes about 15-20 seconds to calculate upon each new selection. The datamodel is neither complex nor large (I'm working with a tiny subset of the real data). Note that the TREATAS comes from the fact that I have a relationship (many:many, if that matters) between my Fact table and a another table ("ASP") where all prices are already calculated per article, company and year-month (it's not connected to the calendar, hence TREATAS). The thinking behing this was simply to avoid summarizing too much on the fly. Data model My questions are: 1) Has anyone ever done this specific kind of analysis; 2) Is there anything you see directly here that should change for a better performance? I will also gladly accept any improvement suggestions. Thanks in advance!
