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
Icon for Community Champion rankCommunity 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