Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

show all peer data for the user in RLS

Hi,

 

I am working on a RLS enabled report. the ask is to show all the records of the customers for which user has the access and show aggregated data for the records for which user doesn't has the access . So, I have created a duplicate copy of the main table/query and removed all non-required columns from the duplicate table/query and created an in-active relationship between these tables/queries.

This report is embeded in a sharepoint portal where user will pass a customer id as a parameter to view the report which works as a report level filter, so the user can see all the records ,for which he has the access, and aggregated data for those records for which he has no access.

I am using two visuals (stacked bar chart) on a page. The first visual shows revenue against the product name for the selected customer id and the second visual should show the revenue against the same product name as per the first visual.

 

 

CustomerIDProductRevenue
1A10
1B20
3A15
3D30
4B25
4C40

 

From the above data sample, if user passes CustomerID = 1 then first visual should show the revenue for products A & B while on the second visual it should show the revenue for product A only for CustomerID 3 and for product B only for CustomerID 4. The product A and B are common with comparison to CustomerID 1.

 

I am trying this dax but getting incorrect values:

 

DAX for the first visual >>

Customer revenue =
var SelCustomerID = SELECTEDVALUE('Table1'[CustomerID],BLANK())
var CustomerRevenue = CALCULATE(SUM('Table1'[Revenue])
                                                                 ,'Table1'[CustomerID] = SelCustomerID)
Return
CustomerRevenue

 

DAX for the second visual >>

Peer revenue =
var SelCustomerID = SELECTEDVALUE('Table1'[CustomerID],BLANK())

var PeerRevenue = CALCULATE(SUM('Table2'[Revenue])
                                                   ,'Table2'[CustomerID] <> SelCustomerID
                                                  , USERELATIONSHIP('Table1'[CustomerID], 'Table2'[CustomerID]))
Return
PeerRevenue

 

Please suggest how to achieve this correctly.

 

Thanks

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try like

    Peer revenue =
    var SelCustomerID = SELECTEDVALUE('Table1'[CustomerID],BLANK())

    var PeerRevenue = CALCULATE(SUM('Table2'[Revenue]), filter('Table2', not 'Table2'[CustomerID]<> SelCustomerID))
    Return
    PeerRevenue

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi amitchandak ,

    Actually this report is RLS enabled and to present all peer's data on the visual i have created a separate view/table and not linked it to the original view/table to avoid RLS. 

    When I do place Product on axis and revenue on value of a stacked bar chart for both the tables, I need the same products on peer's visual as per selected client's visual. As per the dax I am using and you suggested don't work.

    Do I need to something at model level to show the correct values or you can suggest some other dax which can help to achieve the ask.

     

    Thanks