Dax Help

[Seek08]

Hi Community,

I need help on below issue:

In one of report page there is One Field Parameter and One Slicer to toggle between top and bottom to select top 5 and bottom 5. Field Parameters contains 4 values that is Y Axis to 6 bar charts with different KPI's.

I have written 6 different dax for each KPI, that if user select any dimension and Top/Bottom they will be able to see that relevant information. Beow is the code I have written code to ignore any KPI which is blank else rank will be repeated. Below code is for one of the KPI.

Below code is working but to execute it is taking around ~10 Sec. Can anyone please help to optimize this code or any other way to write this code to get same result.

temp =

VAR SelectedTop = 5

var _top= IF ( SELECTEDVALUE ( 'Top/Bottom'[Desc]) = "Bottom", -1, 1 )

var _RankDim1 =

CALCULATE(RANKX(FILTER(ALL('Dim Table1'[Dim1]),not ISBLANK([KPI1])),[KPI1]*_top,,DESC,Dense))

var _RankDim2 = CALCULATE(RANKX(FILTER(ALL('Dim Table2'[Dim2]),not ISBLANK([KPI1])),[KPI1]*_top,,DESC,Dense))

var _RankDim3 = CALCULATE(RANKX(FILTER(ALL('Dim Table3'[Dim3]),not ISBLANK([KPI1])),[KPI1]*_top,,DESC,Dense))

var _RankDim4 = CALCULATE(RANKX(FILTER(ALL('Dim Table4'[Dim4]),not ISBLANK([KPI1])),[KPI1]*_top,,DESC,Dense))

var _dim = SELECTEDVALUE(Dim Selection'[Dimension Order])

RETURN

SWITCH(VALUES('Dimension'[Dimension Order]),

0,IF(_RankDim1<=5,[KPI1],BLANK()),

1,IF(_RankDim2<=5,[KPI1],BLANK()),

2,IF(_RankDim3<=5,[KPI1],BLANK()),

3,IF(_RankDim4<=5,[KPI1],BLANK()))

Thank you!

[Seek08]

Thanks for responding.

I used Rankx(summarize(filter - and filtered out blanks and instead of declarion I used in switch and query time now reduced to ~6 sec. KPI1 is divide(a,b) while a is calculated by sumx(because of conversion multiplication).

Now, lets say KPI1 has same value for 50 rows e.g. 100% for 50 rows out of 1000. Now to filter out just top 5, this is not working as condition is <=5 so all 50 rows are meeting this condition. I checked other community posts with RAND() to break tie, but that is not working as everything is measure here not column. Do we have any solution for such scneraio.

[lbendlin]

What would be your expected result? Can you sort by another field too?

[Seek08]

There is no other field to sort. See below I just need first 5, even if the next sorting is done by Dimension column(e.g. BrandName here).

[lbendlin]

Your requirement cannot be achieved in a meaningful way given the data that you are showing.

[lbendlin]

- show the code for [KPI1]

- don't declare the variables, instead inline the code into the switch statement. That way you cut some of the processing when the switch exits.