Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Top 5 in Rows and Columns

Hi Everyone,   Adding one more situation in the given scenario. (I am using SQL server direct query source hence unable to add new tables in model)   I want to filter category first and then in t...
RANKX
TopN
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Try replacing your ALL statements with ALLSELECTED. ALLSELECTED is kind of tricky but it might work for you. But, the issue is almost certainly your ALL statements.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Greg_Deckler 

     

    Already gone throught  but still unable to create the DAX query or you can say the DAX is not given the correct output.

     

    I am using below DAX but when I am applying Page level Filter (Say in Category as Asset) the result it not accurate.

     

    Please find the DAX 

     

    SpendTop5= 
VAR __topN = 5
VAR __associate = 
    RANKX (
        ALL ( 'Table'[Associate] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Associate] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
VAR __division = 
    RANKX (
        ALL ( 'Table'[Division] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Division] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
RETURN 
    IF( __division && __associate, [spend] )

     

    If you could see, I am applying category filter in my dashboard therefore result is not accurate. I am looking for a DAX that can filter Category and then fetch top5 associate name and their Top5 Division.

     

    Thanks

    Uphar

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Anonymous - Try replacing your ALL statements with ALLSELECTED. ALLSELECTED is kind of tricky but it might work for you. But, the issue is almost certainly your ALL statements.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Greg_Deckler 

         

        Thank you so much for your suggestion. I replaced All with All selected and now able to get the results.

         

        The only change I have done here is that I am only fetching divsion from DAX Top 5 associate names are filtering out through powerbi Top N Filter option and for Category I am filtering through page level filter. 

         

        But its works and giving the correct output.

         

        Below Is the query which I perfromed.

         

        VAR __division = 
    RANKX (
        SelectedALL ( 'Table'[Division] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Division] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
RETURN 
    IF( __division && __associate, [spend] )

         

        Really appreciate

         

        Regards

        Uphar Tandon