Forum Discussion

Prince_Rajkumar's avatar
Prince_Rajkumar
New Member
5 months ago
Solved

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 b...
Need Help
  • Prince_Rajkumar's avatar
    Prince_Rajkumar
    5 months ago

    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.

FBergamaschi's avatar
FBergamaschi
Super User
5 months ago

Hi Prince_Rajkumar 

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

Prince_Rajkumar's avatar
Prince_Rajkumar
New Member
5 months ago

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.

  • johnt75's avatar
    johnt75
    Super User
    5 months ago

    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 ) )