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.

johnt75's avatar
johnt75
Super User
5 months ago

What is the code for [Current Year Premium] and any dependent measures ?

Prince_Rajkumar's avatar
Prince_Rajkumar
New Member
5 months ago

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.