Power Bi and DAX Row Level Security with a Dynamic Table or another mechanism

Question

Forum newbie here, I have a disconnected table (Cost Basis) in my Power Bi report that allows me to dynamically switch my Gross Profit % calculation between two cost structures (Comm-Cost and COGS) based on the user's selection in a slicer. This worked great when all of my user's were allowed to see either cost structure. Now, our company wants to provide access to sales associates and we need to limit these user's to only viewing one cost (Comm-Cost). I have created another table (Cost View) with the user's who we will give access and who we need to limit to only showing the one cost structure.

My slicer is setup using the below measure:

__Dynamic GP% = 
VAR SelectedGP = SELECTEDVALUE ( 'Cost Basis'[Basis], "COGS" )
RETURN
SWITCH ( TRUE (),
SelectedGP = "COGS", [__Gross Profit %],
SelectedGP = "Comm-Cost", [__Gross Profit % COMM],
[__Gross Profit %]
)

I have tried to modify this measure to take into account the sales associate, but I cannot seem to find the correct solution. Please let me know if you need any additional information and thanks for all of your help in advance.

Power Bi Data Model

How do you map UserA to COGS or Comm-Cost? Is it static Roles or Dynamic?(using USERNAME DAX Function) — mxix
It would be static. I currently have the table ‘Cost View’ in my model that would hold all of the associates who should only see the one cost view Comm-Cost. This currently has a relationship with the Sales table based on the User’s ID, but it was only created as I tried to work through a solution. — JBaker0585

Carbon 4horse Carbon 4horse · Accepted Answer · 2019-10-21T10:25:28

If you look the documentation about Swith function https://docs.microsoft.com/en-us/dax/switch-function-dax

You have to use an expression as first parameter. Here you use true() in place of your expression. If your variable SelectedGp return correctly the type.

Replace the switch by this expression :

SWITCH ( SelectedGP,
 "COGS", [__Gross Profit %],
 "Comm-Cost", [__Gross Profit % COMM],
[__Gross Profit %]
)

Power Bi and DAX Row Level Security with a Dynamic Table or another mechanism

1 Answers