Dynamically Group By / Merge Row

[Kashinoda]

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?

[Anonymous]

@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

[Kashinoda]

@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

[Kashinoda]

Bump