Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JKarrick
Frequent Visitor

Object Level Security that is measure dependent

In a tabular model for analysis services, the lowest level of a shared product dimension needs to be hidden from a a group of users if they select inventory measures, but they can see all product columns when selecting sales measures.  There are about 20 product columns and only two need to be hidden for inventory measures. Is it possible to combine object level security with DAX to accomplish this? 

9 REPLIES 9
JKarrick
Frequent Visitor

I appreciate the suggestion of a shadow/hidden table, but please provide more details on how to implement. I'm also providing more specifics about what the model design and desired reporting output for two different user groups. 

 

All Measures (calculated table for all explicit measures)

  • Invoiced Tons comes from Invoice fact table
  • Inventory Tons comes from Inventory fact table

Product dimension (many attributes but limiting to 4 for this example)

  • Product category
  • Product subcategory
  • Reporting grade
  • Material number
    • This column should be visible for Sales users for Invoiced Tons but be hidden for Inventory Tons
    • All other users can see this column with Invoiced and Inventory Tons.

Table relationships:

  • Invoice material number = Product material number
  • Inventory material number = Product material number

Example #1 for Sales user to see the first three Product fields:

JKarrick_1-1701714125264.png

Example #2 - Sales user should not see Material Number for Inventory Tons. Ideally the gray portion would be blank so the same report works for all users instead of completely hidden/access denied that would create an error on the report.

JKarrick_2-1701714326931.png

Example #3 - Non-Sales user can see all product levels:

JKarrick_3-1701714537086.png

 

Thanks for any suggestions!

use two copies of the Product Dimension table.  Join the one with the visible materials column  to the invoices and the one with then hidden materials column to the inventory 

If inventory is joined to the version of the Product dimension with the material column hidden, wouldn't that hide material from all users? Most users should see inventory by material. It's only sales reps that need to have material hidden.

If you have sufficiently diverse audiences then you better create separate reports.

 I'm using Tabular Editor for an Analysis Services cube that is accessed by the entire company in Excel for ad-hoc and some PBI reports. This requirement seems difficult to implement so was hoping some advanced DAX with OLS might make it possible. 

 I'm using Tabular Editor for an Analysis Services cube that is accessed by the entire company in Excel for ad-hoc and some PBI reports. 

Would have been nice if you had led with that.

 

In that case I think you need two cubes.

My original post started with "In a tabular model for analysis services..."

Yes, it did.  I misread it as referring to a Power BI dataset semantic model.

lbendlin
Super User
Super User

Use a shadow/hidden table instead of OLS.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors