Forum Discussion

Kashinoda's avatar
Kashinoda
Advocate I
8 years ago

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

    • Kashinoda's avatar
      Kashinoda
      Advocate 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