I have three tables as below:1) DimGroupS N GroupCode GroupName 1G1Red2G2Green3G3Blue4G4Yellow5G5Black 2) DimUserS NUserCode UserName GroupCode 1U1HarryG12U2SarryG23U3ParryG34U4BarryG15U5GarryG56U6DarryG47U7TarryG18U8ZarryG29U9HurryG310U10KurryG111U11BurryG512U12XurryG413U13IurryG114U14LurryG215U15MurrtG316U16NurryG117U17OurryG518U18PurryG419U19QurryG420U20RurryG121U21SurryG2 3) FactTransactionS NTrnCode UserCode Amount 1T1U556362T2U636613T3U6109554T4U142435T5U340696T6U275937T7U414688T8U153599T9U571510T10U81161611T11U9246812T12U1089513T13U11879014T14U12276715T15U14564916T16U15673917T17U161202018T18U17849419T19U18401520T20U180821T21U2776822T22U3199623T23U41210324T24U54825 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 DimUserHelp please.....

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

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

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

6 Replies

Greg_Deckler

Community Champion

3 years ago

HariBPujari What is your RANKX measure formula?

HariBPujari

Frequent Visitor

3 years ago

Hi Greg_Deckler,

My measures

Rank X by User Transaction =

RANKX(

ALL(DimUser[UserName]),

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

,,DESC,Dense

)

Greg_Deckler

Community 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

Forum Discussion

RANKX with multiple Dimension tables

6 Replies

Related content

RLS on multiple dimensions

Fact Table Multiple Dates Relationship with Dimension Date

Parameter table dimensions - summarize

Same Dimensions, multiple Fact tables, different measures, millions of rows

Dynamic RLS with multiple dimension tables