Forum Discussion

HariBPujari's avatar
HariBPujari
Frequent Visitor
3 years ago
Solved

RANKX with multiple Dimension tables

I have three tables as below: 1) DimGroup S N   GroupCode   GroupName   1 G1 Red 2 G2 Green 3 G3 Blue 4 G4 Yellow 5 G5 Black   2) DimUser S N UserCode   Use...
DAX
power bi
RANKX
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    HariBPujari Try this:

     

    Rank X by User Transaction 2 = 
    VAR __Group = MAX('DimGroup'[GroupCode])
    VAR __User = MAX('DimUser'[UserName])
    VAR __Table = FILTER(SUMMARIZE(ALLEXCEPT('DimUser',DimGroup[GroupName]),[GroupCode],[UserName],"__Value",SUMX(RELATEDTABLE('FactTransaction'),[Amount])),[__Value] <> BLANK())
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank", RANKX(FILTER(__Table,[GroupCode] = EARLIER([GroupCode])),[__Value],,DESC)
        )
    VAR __Result = MAXX(FILTER(__Table1,[GroupCode] = __Group && [UserName] = __User),[__Rank])
RETURN
    __Result

    or this:

    Rank X by User Transaction 2 = 
    VAR __Group = MAX('DimGroup'[GroupCode])
    VAR __User = MAX('DimUser'[UserName])
    VAR __Table = SUMMARIZE(ALLEXCEPT('DimUser',DimGroup[GroupName]),[GroupCode],[UserName],"__Value",SUMX(RELATEDTABLE('FactTransaction'),[Amount]))
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank", RANKX(FILTER(__Table,[GroupCode] = EARLIER([GroupCode])),[__Value],,DESC)
        )
    VAR __Result = MAXX(FILTER(__Table1,[GroupCode] = __Group && [UserName] = __User),[__Rank])
RETURN
    __Result

     

HariBPujari's avatar
HariBPujari
Frequent Visitor
3 years ago
My measures
 
Rank X by User Transaction =
RANKX(
    ALL(DimUser[UserName]),
    SUMX(RELATEDTABLE(FactTransaction),FactTransaction[Amount])
    ,,DESC,Dense
)
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    HariBPujari Try this:

     

    Rank X by User Transaction 2 = 
    VAR __Group = MAX('DimGroup'[GroupCode])
    VAR __User = MAX('DimUser'[UserName])
    VAR __Table = FILTER(SUMMARIZE(ALLEXCEPT('DimUser',DimGroup[GroupName]),[GroupCode],[UserName],"__Value",SUMX(RELATEDTABLE('FactTransaction'),[Amount])),[__Value] <> BLANK())
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank", RANKX(FILTER(__Table,[GroupCode] = EARLIER([GroupCode])),[__Value],,DESC)
        )
    VAR __Result = MAXX(FILTER(__Table1,[GroupCode] = __Group && [UserName] = __User),[__Rank])
RETURN
    __Result

    or this:

    Rank X by User Transaction 2 = 
    VAR __Group = MAX('DimGroup'[GroupCode])
    VAR __User = MAX('DimUser'[UserName])
    VAR __Table = SUMMARIZE(ALLEXCEPT('DimUser',DimGroup[GroupName]),[GroupCode],[UserName],"__Value",SUMX(RELATEDTABLE('FactTransaction'),[Amount]))
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank", RANKX(FILTER(__Table,[GroupCode] = EARLIER([GroupCode])),[__Value],,DESC)
        )
    VAR __Result = MAXX(FILTER(__Table1,[GroupCode] = __Group && [UserName] = __User),[__Rank])
RETURN
    __Result

     

    • HariBPujari's avatar
      HariBPujari
      Frequent Visitor
      3 years ago

      Wow,

      It worked, thanks.

      I thought it will be easy as short and sweet. But there is lots of DAX code.

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

        HariBPujari Well, not saying it is the only solution, but it is at least one solution to the problem.