Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

RANKX with ALLSELECTED AND ALLEXCEPT?

Hello,

 

I have one big  datatable with the following columns

 

DateEANPD:PRODUCT EAN-NameCATEGORYLANGUAGEVALIDIDRANDOM
01.01.2020123P1CAREDEx1
01.02.20201234P2SPECIAL CARECNx1,5
07.01.20201234P2SPECIAL CARECNx1,8
04.01.2020123P1CAREENx2
01.01.202012345P3SPECIAL CARECNx1

 

VALIDID is a calculated column through which power BI decides what EAN is valid

 

I am using the following RANKX function:

 

RANKING SCANNER = RANKX(ALLSELECTED('Data'[PD: Product EAN-Name]),[PRODUCTSCAN],,DESC)
PRODUCTSCAN = SUMX('MatomoDataNewv 1','Data'[RANDOM])
 
Ranndom is a calculated column which is: RANDOM = 1+RAND()
 
Now I would simply display the data in a table including the Rank and the Product EAN Name and filter thevisual based on language. There is also a ReportFilter for all visuals which is VALIDID = X
 
 
This seems to work as there are no doublecountings since I am using RANDOM. All goood so far.
 
Now I would like to only display the top 5 Products which I thought I can do through visual filter on my my ranking measure  RANKING SCANNER. This doesn't seem work.
It seems that I am changing the context on which the ranking is based on or something as the result lookslike this.
 
PS = RANKING SCANNER
 
 How can this be? Is there another solution for example using ALLSELETED with ALLEXCEPT ?
 
Thank you in advance for any hint
Need Help
Tips and Tricks

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous Just a thought, what if you created a second, duplicate measure and used that in your filter criteria instead of the same measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

       

      Thanks for the hint but this did not help. Can this have something to do with the filter I am using before loeading the data in to my model? Because before I am loading the data I am filtering out invalid rows through the category column within in the query view(Table tools)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I won't suggest using the filter pane to do the top 5, when I do this in my dashboard, I will use:

     

    measure = if(RANKX(ALLSELECTED('Data'[PD: Product EAN-Name]),[PRODUCTSCAN],,DESC) <= 5,
RANKX(ALLSELECTED('Data'[PD: Product EAN-Name]),[PRODUCTSCAN],,DESC),
blank())

     

    Then the table will show the top 5 automatically.

     

    Please try.

    Aiolos Zhao 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

    I think you have to add rand to rank

     

    RANKING SCANNER = RANKX(ALLSELECTED('Data'[PD: Product EAN-Name]),[PRODUCTSCAN],,DESC) + rand()

     

    and then use a visual level filter or Top N again on this

     

    Refer how it works :https://www.youtube.com/watch?v=kRtmb8ftyQc