Forum Discussion
Need Performance Improvement Suggestions
My Model is built like a star schema
Fact: Sales
Dimension: Date, Customer, Customer Group, Classification, Policy, Calculation Type
I have a Table visual in Power BI that shows Top customers based on Premium.
It has Customer Name, CY Premium, PY Premium.
The Dax I have used is
Top N Display =
VAR vTopN = selectedvalue('Top'[TopN])
VAR vPremium = [Current Year Premium]
VAR vRank =
IF( NOT ISBLANK(vPremium), Rankx(All Selected(customers), [Current Year Premium], vPremium , Desc, Dense))
Return
if (vRank<=vTopN, vPremiumThis returns the premium for Top customers based on slicer on TopN, i am using a visual level filter to filter out blank values.
On the same page I have another table where i need to show all the policies of the top customers along with some additional details (lowest grain).
If i introduce additional related fields from all the related Dimension tables, the table is taking more than 20 mins to display the results.
Since the first table have only customer and Premium, this loads in less than 30 seconds.
During Development i tested with 2000+ records and the results were faster for the second table visual, now my actual fact data has more than 20000+ records.
Could someone help me with improving the perfomance on the second visual.
Hi All,
Thank you for your suggestions. johnt75 My model doesnt follow the standard date calendar, hence i cant use any of the time intelligence functions.
I have rewritten my DAX again - now i am getting results within less than 30 seconds, here is my new code.
Top N Display = VAR vTopN = selectedvalue('Top'[TopN]) VAR vPremium = [Current Year Premium] VAR vRank = IF( NOT ISBLANK(vPremium), Rankx(All Selected(customers[Customer Name]), Calculate( [Current Year Premium], AllSelected(Date), All(Policy), AllSelected('Calculation Type'), AllSelected('Customer Group') ), , Desc, Dense)) Return if (vRank<=vTopN, vPremium)Thanks everyone for your suggestions. I was unable to share the PBIX since my model was using live connection to a dataset.
8 Replies
What is the code for [Current Year Premium] and any dependent measures ?
Hi,
Here is my measure and the dependent Measures
Current Year Premium = VAR vCYMth = Max('Date'[Year Month]) VAR vCY = VALUE(LEFT(vCYMth,4)) RETURN CALCULATE( [Premium Base Measure], Filter( All('Date'), 'Date'[Year Month] <=vCYMth, 'Date'[Year] = vCY ) ) Premium Base Measure = CALCULATE(SUM('Fact'[Premium]),'Classification'[Category 1] = 'Valid Records')The measure passed in my YTD measure.
Hi,
Share the download link of the PBI file.
It’s difficult to determine the exact cause of the performance issue without being able to inspect the data model itself. Factors such as the model design, relationships between tables, cardinality of the dimension tables, the number of columns being brought into the visual, and how the measures are written can all significantly affect query performance. In particular, visuals that operate at a lower grain and pull fields from multiple related dimension tables can generate much more complex queries compared to a summarized table. Reviewing the model structure, relationship directions, and the query plan using tools like Performance Analyzer or DAX Studio would likely be necessary to properly identify the bottleneck.
as the others suggested, it is very difficult to help on perormance just with some DAX code. We need also to see the model, the visual arrangedmemts etc. Please provide what you were asked to help us help you
Though, there are two things that might be identified already
1 - please provide the DAX code of Current Year Premium just for checking for any bad practice there
2 - in the variable vRank you should avoid the entire customers table, so I suggest to rearrange it in this way
VAR vRank =
IF (
NOT ISBLANK (vPremium),
RANKX ( ALLSELECTED ( Customers[CustomerKey] ), [Current Year Premium], DESC, DENSE )
)The above should anyway help, rest to check if the measure on point 1 (which has impact on point 2 as well and then the model). Please let me know if point 2 helps a bit
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Thank you for the suggestion. I have made this change. RANKX ( ALLSELECTED ( Customers[CustomerKey] ), [Current Year Premium], DESC, DENSE )
Here is my measure and the dependent Measures
Current Year Premium = VAR vCYMth = Max('Date'[Year Month]) VAR vCY = VALUE(LEFT(vCYMth,4)) RETURN CALCULATE( [Premium Base Measure], Filter( All('Date'), 'Date'[Year Month] <=vCYMth, 'Date'[Year] = vCY ) ) Premium Base Measure = CALCULATE(SUM('Fact'[Premium]),'Classification'[Category 1] = 'Valid Records')The measure passed in my YTD measure.
You could simplify the code to
Current Year Premium = CALCULATE ( [Premium Base Measure], DATESYTD ( 'Date'[Date] ) )And you prior year premium could be
Prior Year Premium = CALCULATE ( [Current Year Premium], DATEADD ( 'Date'[Date], -1, YEAR ) )
Hi All,
Thank you for your suggestions. johnt75 My model doesnt follow the standard date calendar, hence i cant use any of the time intelligence functions.
I have rewritten my DAX again - now i am getting results within less than 30 seconds, here is my new code.
Top N Display = VAR vTopN = selectedvalue('Top'[TopN]) VAR vPremium = [Current Year Premium] VAR vRank = IF( NOT ISBLANK(vPremium), Rankx(All Selected(customers[Customer Name]), Calculate( [Current Year Premium], AllSelected(Date), All(Policy), AllSelected('Calculation Type'), AllSelected('Customer Group') ), , Desc, Dense)) Return if (vRank<=vTopN, vPremium)Thanks everyone for your suggestions. I was unable to share the PBIX since my model was using live connection to a dataset.
