Advanced DAX Optimization: How Do You Improve Performance in Complex Power BI Semantic Models?
As Power BI solutions grow, DAX measures can become increasingly complex, especially when working with large datasets, multiple fact tables, complex relationships, and dynamic filter contexts. A report may work perfectly with a small dataset but experience slow visual rendering and high memory consumption when deployed with millions of records. I'd like to understand how experienced Power BI developers approach these challenges. **Some key questions:** 1. **DAX Optimization:** How do you optimize complex measures involving nested CALCULATE(), FILTER(), SUMX(), and iterators? 2. **Filter Context:** How do you troubleshoot unexpected results caused by context transition, ALL(), REMOVEFILTERS(), and ALLEXCEPT()? 3. **Model Design:** When should you use a star schema instead of a more complex relationship structure? 4. **Performance Analyzer:** How do you identify whether slow visuals are caused by DAX query execution, model relationships, or storage engine operations? 5. **Large Semantic Models:** What strategies do you use to optimize VertiPaq compression and reduce memory consumption? 6. **DirectQuery vs Import:** How do you decide between Import, DirectQuery, and Direct Lake for large-scale reporting solutions? **My main challenge:** Suppose a Power BI report contains 50+ complex DAX measures, multiple fact tables, and more than 20 million rows. Some visuals take 15–20 seconds to load. What systematic approach would you follow to identify bottlenecks and optimize the semantic model without changing the business logic or compromising calculation accuracy? I'd appreciate real-world examples, advanced DAX techniques, and performance optimization best practices.
Average of categories over vendor
Hello, I'm trying to calculate the category average of vendors, but view the specific vendor. I need to make my formula calculate the score of the category that the selected vendor is in. It currently is showing the same average value for all 4 categories, when they should be different. What do I need to change in my formula? %_VendorScore_Avg_Category = VAR brand = 0.14 VAR eob = 0.13 VAR finance = 0.17 VAR service = 0.18 VAR supplychain = 0.14 VAR technology = 0.09 VAR opportunity = 0.15 VAR SelectedCategory = SELECTEDVALUE ( 'Vendor Scorecard'[Category] ) RETURN CALCULATE ( ([#_BrandScore_Avg] * brand) + ([#_EaseofBusiness_Avg] * eob) + ([#_Finance_Avg] * finance) + ([#_Service_Avg] * service) + ([#_SupplyChain_Avg] * supplychain) + ([#_Technology_Avg] * technology) + ([#_Opportunity_Avg] * opportunity), REMOVEFILTERS ('Vendor Scorecard'[Vendor Name]), 'Vendor Scorecard'[Category] = SelectedCategory )
DAX
Bonjour les experts dax j'ai importé une table 'sinistres_regles' qui est une jointure de tables en SQL j'ai écrit une mesure pour calculer le nombre de dossier en stock, le résultat match bien avec l'équivalent de ma requête SQL j'ai écrit une mesure pour calculer le coût des sinistres, le résultat matche avec celui de la requête SQL existante par contre quand je calcule le coût des sinistres en stock , le résultat est très différent de celui de la BD. Mes requêtes SQL utilisent les mêmes jointures de tables que ma table 'sinistres regles' dans POWER BI comme base du FROM. je joins à mon message les captures des scripts dxa que j'ai écrit.
DAX formula for Monthly Cumulative Backlog Ticket Calculation
Hello, i have found a response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i think the method described of solving the problem is close to the one i m looking for, but in my attempt to download the pbi file and see the calculation, the file is missing. https://www.linkedin.com/safety/go?url=https%3A%2F%2Fcommunity.fabric.microsoft.com%2Ft5%2FDAX-Commands-and-Tips%2FNeed-help-to-create-DAX-formula-for-monthly-backlog-ticket%2Ftd-p%2F1651032&trk=flagship-messaging-web&messageThreadUrn=urn%3Ali%3AmessagingThread%3A2-OTkwNjEwMTYtOTMwNS00MzJkLThmNDQtMjMzNjUwNTFjYTEzXzAxMw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BlGvr8tgdR92f5F9Hpj%2BQRg%3D%3D Futhermore some more information on what i need to achieve and how the solution you provided is close but needs something more to be completed. The Final Output of the report i m trying to implement is the following. The Backlog calculation should provide a cummulative sum of the tickets that remain open (Status = Open) and be added on the following month. For example: On September 2023 Open Tickets (45) - Closed Tickets (23) + Backlog (1) = 23. On your previous response regarding the backlog calculation, i notice that the dax formula doesnt take in consideration the previous backlog tickets if on the previous month none was opened or complete. Year Month Opened_Tickets Closed_Tickets Backlog 2023 June 1 0 1 2023 July 3 3 1 2023 August 0 0 1 2023 September 45 23 23 2023 October 68 51 40 2023 November 72 38 74 2023 December 59 36 97 2024 January 127 69 155 2024 February 135 64 226 2024 March 104 105 225 2024 April 216 120 321 2024 May 138 237 222 2024 June 5 5 222 A few things on my raw data and tables that i need to do the calculation on. Table 1 = Jira Raw that contains the following Columns: Key Status Created Resolved Τckt-1043 Open 3/6/2024 16:41 Τckt-1042 Open 3/6/2024 14:30 5/6/2024 14:30 Τckt-1041 Open 3/6/2024 12:43 My calendar Table is called Calendar [Date] that contains year,quarter,month, week and weekday. My relationships look as follows : Active relationship between Calendar [Date] and Jira raw [created] Inactive relatonship between Calendar [Date] and Jira raw [Resolved] As far as my calculations : Opened_Tickets = if(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))), 0, CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))) Closed_Tickets = IF(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))),0,CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))) Backlog = CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Created] <= max('Calendar'[Date]) || not(ISBLANK(Jira_Raw[Created])))) - CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Resolved] <= max('Calendar'[Date]) && not(ISBLANK(Jira_Raw[Resolved])))) Last but not least i m new to the whole dax and power BI world so any help on achieving the correct calculation of the backlog should be much appreciated. Best Regards.
Need a Dax to make a measure for counting the rows on a dynamic table using 2 parameters [Power BI]
The Row count needs to be displayed on a Key card next to the table visual. The Data of the Table is from two Parameters (Multi-selection) : 'Selected Dimensions P3'[Select Dimensions] 'Selected Measures P3'[Select Measures] Rows on the Table keeps changing according to the field selection by the viewer. The row count on the key card should simultaneously change to the current number of rows displayed on Table Visual.
