Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am working on a project that involves calculation using relationship on multiple columns of fact table through USERELATIONSHIP() and also apply RLS on dimension table and it fails, as advised in the documentation (https://docs.microsoft.com/en-us/dax/userelationship-function-dax). I am looking for alternate ways to achieve this. See example below
Dimension Table
User Name | User Email
AAA aaa@company.com
BBB bbb@company.com
CCC ccc@company.com
DDD ddd@company.com
* Dimension table uses RLS on 'User Email' with the following filter where [user email] = USERPRINCIPALNAME() so that when aaa logins it is only able to see his details in fact table.
Fact Table
Month | Revenue | Lead | Support
Jan 100 AAA CCC
Feb 80 BBB AAA
Mar 150 CCC BBB
RELATIONSHIPS
Active Relationship = DimensionTable[User Name] 1 --> * FactTable[Lead]
Inactive Relationship = DimensionTable[User Name] 1 --> * FactTable[Lead]
OBJECTIVE OF CALCULATION
User AAA should see the total revenue where he is either 'Lead' or 'Support' i.e. 180 (100+80). Because there could be only 1 active relationship at one time. Following measure was used to achieve this:
DAX MEASURE
Total Revenue =
VAR LeadRev = CALCULATE(SUM('fact'[Revenue]), USERELATIONSHIP(DimensionTable[User Name], FactTable[Lead]))
VAR SupportRev = CALCULATE(SUM('fact'[Revenue]), USERELATIONSHIP(DimensionTable[User Name], FactTable[Support]))
RETURN LeadRev + SupportRev
CHALLENGE
The calculation works perfectly fine but the moment I apply RLS on DimensionTable, this whole thing fails. This is documented in userelationship() documentation https://docs.microsoft.com/en-us/dax/userelationship-function-dax.
SOLUTION REQURED
Looking for alternate to achieve the same thing without breaking RLS.
Solved! Go to Solution.
Hi @Anonymous ,
In the Document mentioned: USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included.
I think the data of fact table the role get depends on the default active relation, you can edit the active status of two relations and find the output is different (the published report will have only one active relation between two tables, you can find different result because you have the premission of edit)
If you have just two tables, you can add RLS filter in the fact table, But if you have multi tables, you can use the workaround like following:
1. create a calculate table(if you just want the grand total regardless of month, you can make it easier)
Table = FILTER ( ADDCOLUMNS ( SELECTCOLUMNS ( CROSSJOIN ( DISTINCT ( 'Fact Table'[Month] ), DISTINCT ( 'Dimension Table'[User Name] ) ), "Month2", [Month], "Name", [User Name] ), "SumRevenue", SUMX ( FILTER ( 'Fact Table', AND ( 'Fact Table'[Month] = [Month2], OR ( 'Fact Table'[Lead] = [Name], 'Fact Table'[Support] = [Name] ) ) ), [Revenue] ) ), NOT ISBLANK ( [SumRevenue] ) )
SimpleTable = FILTER ( ADDCOLUMNS ( DISTINCT ( 'Dimension Table'[User Name] ), "SumRevenue", SUMX ( FILTER ( 'Fact Table', OR ( 'Fact Table'[Lead] = [User Name], 'Fact Table'[Support] = [User Name] ) ), [Revenue] ) ), NOT ISBLANK ( [SumRevenue] ) )
2. make relation between the table and Dimension table
3. using the table (or make measure if you need)
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
good post, thanks all.
Hi @Anonymous ,
In the Document mentioned: USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included.
I think the data of fact table the role get depends on the default active relation, you can edit the active status of two relations and find the output is different (the published report will have only one active relation between two tables, you can find different result because you have the premission of edit)
If you have just two tables, you can add RLS filter in the fact table, But if you have multi tables, you can use the workaround like following:
1. create a calculate table(if you just want the grand total regardless of month, you can make it easier)
Table = FILTER ( ADDCOLUMNS ( SELECTCOLUMNS ( CROSSJOIN ( DISTINCT ( 'Fact Table'[Month] ), DISTINCT ( 'Dimension Table'[User Name] ) ), "Month2", [Month], "Name", [User Name] ), "SumRevenue", SUMX ( FILTER ( 'Fact Table', AND ( 'Fact Table'[Month] = [Month2], OR ( 'Fact Table'[Lead] = [Name], 'Fact Table'[Support] = [Name] ) ) ), [Revenue] ) ), NOT ISBLANK ( [SumRevenue] ) )
SimpleTable = FILTER ( ADDCOLUMNS ( DISTINCT ( 'Dimension Table'[User Name] ), "SumRevenue", SUMX ( FILTER ( 'Fact Table', OR ( 'Fact Table'[Lead] = [User Name], 'Fact Table'[Support] = [User Name] ) ), [Revenue] ) ), NOT ISBLANK ( [SumRevenue] ) )
2. make relation between the table and Dimension table
3. using the table (or make measure if you need)
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @v-lid-msft,
Thank you very much for your detailed instruction. Could you please upload the pbix file again? The link you provided, I think, is broken (or cannot be accessed by other people?).
Thanks!
Hi @darwindat ,
Pbix File as attached.
Best regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |