topic Re: RANKX with multiple Dimension tables in DAX Commands and Tips

RANKX with multiple Dimension tables

HariBPujari — Sun, 05 Feb 2023 16:04:56 GMT

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.....

Re: RANKX with multiple Dimension tables

Greg_Deckler — Sun, 05 Feb 2023 15:56:49 GMT

HariBPujari What is your RANKX measure formula?

Re: RANKX with multiple Dimension tables

HariBPujari — Sun, 05 Feb 2023 16:03:33 GMT

Hi Greg_Deckler,

My measures

Rank X by User Transaction =

RANKX(

ALL(DimUser[UserName]),

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

,,DESC,Dense

)

Re: RANKX with multiple Dimension tables

Greg_Deckler — Sun, 05 Feb 2023 16:23:27 GMT

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

Re: RANKX with multiple Dimension tables

HariBPujari — Sun, 05 Feb 2023 17:23:55 GMT

Wow,

It worked, thanks.

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

Re: RANKX with multiple Dimension tables

Greg_Deckler — Sun, 05 Feb 2023 17:29:52 GMT

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

Re: RANKX with multiple Dimension tables

CNENFRNL — Sun, 05 Feb 2023 23:55:23 GMT

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