Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Replacing transaction values with null where the data doesnt belong to the user

Hi 

 

Apologies if this has been covered in another post.

 

I am trying to find a way in which the fact table isn't filtered using RLS and instead show a null transaction value but keep the attribute data. The reason being, employees need to see all transactions but only need to see the values of those transactions if it belongs to them. I'd also want to scale this up through managers too. 

 

fact table:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

dim table:

 

 

 

 

 

 

 

 

 

 

 

 

 

John Smith (IM) can see:

 

 

 

 

 

 

 

 

David Davis (BDM) can see:

Need Help

1 Reply

  • croberts21's avatar
    croberts21
    Continued Contributor
    2 years ago

    We are struggling with a similar issue. To use RLS we would have to buy a PBI license for each of 25 people that would use a report and we are not ready for that.

     

    But for your needs, somehow PBI needs to get the current employee key without asking for user input, then use a formula for your "Value" column which I will rename "Myvalue" below. And "Currentkey" is the Employee Key that PBI finds for the current user. 

    Myvalue=IF(Currentkey=[Employee Key],Value,"")

     

    So if the Currentkey is the same as [Employee Key] then show the real Value, otherwise show a blank string.

     

    We have done a workaround with a PBI report and a PBI Pagination Report where we go through a loop of people, make a report just with their data, and email a spreadsheet of the data to them once a week.