RANKX with multiple Dimension tables

[HariBPujari]

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	UserName	GroupCode
1	U1	Harry	G1
2	U2	Sarry	G2
3	U3	Parry	G3
4	U4	Barry	G1
5	U5	Garry	G5
6	U6	Darry	G4
7	U7	Tarry	G1
8	U8	Zarry	G2
9	U9	Hurry	G3
10	U10	Kurry	G1
11	U11	Burry	G5
12	U12	Xurry	G4
13	U13	Iurry	G1
14	U14	Lurry	G2
15	U15	Murrt	G3
16	U16	Nurry	G1
17	U17	Ourry	G5
18	U18	Purry	G4
19	U19	Qurry	G4
20	U20	Rurry	G1
21	U21	Surry	G2

3) FactTransaction

S N	TrnCode	UserCode	Amount
1	T1	U5	5636
2	T2	U6	3661
3	T3	U6	10955
4	T4	U1	4243
5	T5	U3	4069
6	T6	U2	7593
7	T7	U4	1468
8	T8	U1	5359
9	T9	U5	715
10	T10	U8	11616
11	T11	U9	2468
12	T12	U10	895
13	T13	U11	8790
14	T14	U12	2767
15	T15	U14	5649
16	T16	U15	6739
17	T17	U16	12020
18	T18	U17	8494
19	T19	U18	4015
20	T20	U1	808
21	T21	U2	7768
22	T22	U3	1996
23	T23	U4	12103
24	T24	U5	4825

Relationships:

1) DimGroup[GroupCode] 1->* DimUser[GroupCode]

2) DimUser[UserCode] 1->* FactTransaction[UserCode]

Here, I am trying to create a measure RANKX by FactTransaction[Amount] and put it in the matrix visual with GroupName from DimGroup and UserName from DimUser. But the matrix shows all UserName for each GroupName. But if I put GroupCode from DimUser, it works as expected but i want GroupName from DimGroup.

My Measure:

RANKX(

    ALL(DimUser[UserName]),

    SUMX(RELATEDTABLE(FactTransaction),FactTransaction[Amount])

    ,,DESC,Dense

)

Correct one by need GroupName from DimGroup instead GroupCode from DimUser

Help please.....

[Greg_Deckler]

@HariBPujari What is your RANKX measure formula?

[HariBPujari]

Hi Greg_Deckler,

My measures

Rank X by User Transaction =

RANKX(

    ALL(DimUser[UserName]),

    SUMX(RELATEDTABLE(FactTransaction),FactTransaction[Amount])

    ,,DESC,Dense

)

[Greg_Deckler · Accepted solution]

@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]

Wow,

It worked, thanks.

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

[CNENFRNL]

Of coz it could be concisely coded. Keep this question open to see if there would be any solution more elegant.

[Greg_Deckler]

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