Guy(s) in a Cube present | Power BI tips to take performance to the next level!
For the 1st Health & Life Sciences Power BI User Group event, Guy(s) in a Cube will be sharing tips and tricks on how to boost performance. Want to make your Power BI reports perform at insane amazing speeds? We will explore some practical and common performance problems that customers encounter when using Power BI. From report design to your data, we will cover different aspects of Power BI to get your report working at the level that you expect. There are different settings and techniques you can use to maximize your data and reports within Power BI. Adam Saxton is just a guy in a cube doing the work! He is on the Power BI CAT team at Microsoft working with customers to help adopt Power BI. He is based in Texas and started with Microsoft supporting SQL Server connectivity and Reporting Services in 2005. Adam has worked with Power BI since the beginning, on the support side and with documentation. In addition, he produces weekly videos for his Guy in a Cube YouTube channel. LinkedIn Patrick LeBlanc is currently a Principal Program Manager at Microsoft and a contributing partner to Guy in a Cube. Along with his 15+ years' experience in IT, he holds a Master of Science degree from Louisiana State University. He is the author and co-author of five SQL Server books. Prior to joining Microsoft, he was awarded Microsoft MVP for his contributions to the community. Patrick is a regular speaker at many SQL Server Conferences and Community events. LinkedInMicrosoft Fabric vs Snowflake
Le 11 décembre 2024, nous vous proposons un live unique consacré à une comparaison approfondie entre deux des plus grandes plateformes de données du moment : Microsoft Fabric et Snowflake. Nous vous proposons, lors de ce live, une analyse comparative entre Microsoft Fabric et Snowflake. Sujet complexe et souvent débattu, nos experts vous offriront un retour honnête et bienveillant basé sur leurs tests et expériences respectifs, afin de vous aider à faire un choix éclairé. Grâce à la diversité des points de vue et à la complémentarité de nos intervenants, nous arriverons à une conclusion équilibrée et pertinente, sur laquelle tous trois s'accordent. Les speakers : Frédéric Brossard, spécialiste de l'implémentation de Snowflake, partagera ses expériences et ses insights sur cette plateforme de données en pleine expansion. Marie Aubert et Charles-Henri Sauget, experts Microsoft Fabric, vous dévoileront les atouts et les fonctionnalités de cette solution innovante.Zürich - 68th Fabric User Group [ONLINE]
Dear Data Wizards, We are looking forward to inviting all of you to our next meetup. This time in online mode. Topics What's New - Kristian Power BI Performance - 7 Deadly Sins! - Nikola The session will be recorded and made available on YouTube --> https://aka.ms/FabricUGYouTube Power BI Performance - 7 Deadly Sins! Congratulations! You've built your shiny fancy Power BI report and your charts look terrific. But, your data refresh takes forever...Or, your report is utterly slow when interacting with visuals...You switch to DirectQuery mode to make things better, just to discover that the report performance sucks even more... And, if you feel lost, I don't blame you - I've also been there! That's why this session is for you: we will examine potential bottlenecks in your Power BI report - starting with defining the best practices for data model size optimization, understanding the importance of query folding feature, all the way to identifying culprits for your slow report rendering. We will wrap it up with learning when and why (not) to use DirectQuery mode, and how to apply improvements on the data source side to make things faster in the Power BI. Good to know We want this group to be a safe environment that encourages open discussion, exchange of ideas and problems you may face. Therefore, we kindly ask that no members will leverage the information for unsolicited acquisitions of new customers or projects. This group builds on trust, and without it we cannot learn from each other and excel on this topic. Want to be a presenter? We are always looking for new speaker. If you are interested and would like to show something to the Power BI Meetup Group please feel free to contact us!
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() )
