Forum Discussion
Dynamically Group By / Merge Row
Hi All,
I have tried to create a model which implements some basic 'Column' level security.
Essentially the columns that require security are placed in a seperate table which has some RLS rules,
I then have a measure which calls values from the secure table using ISFIRSTNONBLANK and USERELATIONSHIP, if there are BLANK values (i.e. the user has no access) it returns the string '(CONFIDENTIAL)'.
This solution works well:
Here is the model:
Here is the measure:
ProductDescription =
IF (
ISBLANK (
CALCULATE (
CALCULATE ( LASTNONBLANK ( FactProductConfidential[ProductDescription], "" ) ),
USERELATIONSHIP ( FactProductConfidential[FKProduct], DimProduct[SKProduct] ),
ALLSELECTED ( FactProductConfidential )
)
),
"(CONFIDENTIAL)",
CALCULATE (
CALCULATE ( LASTNONBLANK ( FactProductConfidential[ProductDescription], "" ) ),
USERELATIONSHIP ( FactProductConfidential[FKProduct], DimProduct[SKProduct] ),
ALLSELECTED ( FactProductConfidential )
)
)
Here is the PBIX:
https://drive.google.com/file/d/1M0z40xBMysrEtfYAGCYzer1HTvgpybMY/view?usp=sharing
My issue is I'd like to group/merge all the '(CONFIDENTIAL)' results together and I keep going in loops to solve this.
I think a big problem is DimProduct[ProductNumber] (or SKProduct) need to be present on the report to give the row context to the [ProductDescription] measure.
Anyone have any ideas?
3 Replies
- AnonymousNot applicable
Kashinoda,
How about you create a calculated column or summarized table instead? Please check the DAX in the modified PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1Noky93sU9skg-CUQJ5
Regards,
Lydia- KashinodaAdvocate I
Anonymous
Thanks for your reply, unfortunately a calculated column in the DimProduct table wont work as this will only be populated on process.
Different users will have different access to FactProductConfidential[ProductDescription], this is defined in the 'ConfidentialAccess' table. You can see this yourself by selecting roles 'Everyone' and 'Confidential' and entering 'Other User' as either GHD\mikeshinoda or GHD\janeaustin
- KashinodaAdvocate I
Bump