Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

List Top N at 2nd Row Field

Hello everyone,

 

Is there a way to filter a table or matrix visual by applying Top N at 2nd row field?

The Sample PBI File is here. 

Here is the raw visual without filter:

 

Expect Result:

List only Top 3 [Page] at 2nd Row by Sum of Final Calculation (which is Impressions)

 

Here is the Top 3 filter applied, apparently, it's not correct on "Page".

 

The DAXs are:

 

Measure = 
VAR inPR =
SELECTEDVALUE('Progress Record'[Campaign Page ID])
Return
calculate(sum(Raw[Impressions]),
        FILTER ( Raw
        , SEARCH (inPR, 'Raw'[Page], 1, -1 ) > 0 ))
Final Calculation = SUMX(VALUES('Progress Record'[Campaign Page ID]),'Progress Record'[Measure])

 

 

 

Do I have to update the DAX or if there is a way in PBI can proceed the direct filtering?

Thanks in advance for any of your idea.

H

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    I am not sure how to fix this Page top 3 by another measure as it seems to ignore everything but get Top 3...I have another way, simply RANKX then filter it

     

    Ranking = 
RANKX(ALL('Raw'[Page]),[Final Calculation],,DESC)
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Anonymous 

    According to your description, I can roughly understand your requirement, I think you can create this measure and apply as the visual filter of this matrix to achieve this:

    Rank =

var _table=SUMMARIZE(ALL('Raw'),Raw[Page],"1",[Final Calculation])

return

RANKX(_table,[Final Calculation],,DESC,Dense)

    Then you can apply the visual filter like this:

     

    And you can get what you want.

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    I am not sure how to fix this Page top 3 by another measure as it seems to ignore everything but get Top 3...I have another way, simply RANKX then filter it

     

    Ranking = 
RANKX(ALL('Raw'[Page]),[Final Calculation],,DESC)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you Anonymous for the quick and easy solution!

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    According to your description, I can roughly understand your requirement, I think you can create this measure and apply as the visual filter of this matrix to achieve this:

    Rank =

var _table=SUMMARIZE(ALL('Raw'),Raw[Page],"1",[Final Calculation])

return

RANKX(_table,[Final Calculation],,DESC,Dense)

    Then you can apply the visual filter like this:

     

    And you can get what you want.

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you v-robertq-msft  for the solution and file, very helpful!