Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax code for Row level security

I have a cube in SSAS.in the cube i have a single table. I have category and MCO columns are there.in the category i have different product values are like "tea,coffe and MCO different values are there like in below i attached so i created a role. in the role i dont want show tea related some of MCO values when user login in service.I want show for tea category- MCO(india,indenosia,nepal countries). Is this possiable to create RLS?

=IF(SELECTEDVALUE(BPPlusReport[Category])="Tea",ISFILTERED(BPPlusReport[MCO]) in {"India","Nepal","Indonesia"}) I tried this but it is not working.

 

 

  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hi again Anonymous 

    The initial equals sign isn't needed when entering the Table filter DAX expression in Power BI Desktop.

    If you remove the equals sign does it work?

     

    Regards,

    Owen

9 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    Hi Anonymous 

     

    With RLS, the DAX expression is evaluated in the row context of the relevant table.

    For this reason, you can directly reference columns, with no need for SELECTEDVALUE or ISFILTERED.

     

    A concise way of stating your condition is this (hopefully I understood correctly):

    OR (
    BPPlusReport[Category] <> "Tea",
    BPPlusReport[MCO] IN { "India", "Nepal", "Indonesia" }
)

    This would include all non-Tea, plus Tea only for the three MCO listed.

     

    If you like, you can test this out by creating a calculated column with the same expression, filtering it to TRUE, and checking if the expected rows are visible.

     

    Let me know if this works.

     

    Regards,

    Owen

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Owen

       

      it is working when i created the column and filtered for tea but when I applied same DAX in Roles it is showing syntax error. Please find below screen shot.

      Thanks

      Ruthwik

      • OwenAuger's avatar
        OwenAuger
        Super User
        4 years ago

        Hi again Anonymous 

        The initial equals sign isn't needed when entering the Table filter DAX expression in Power BI Desktop.

        If you remove the equals sign does it work?

         

        Regards,

        Owen

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Owen,

     

    But  I have multiple fact tables are in modeling and have realtionship between dim tables and fact tables. I am using slicers category and MCO columns are from dim tables only. Is i need create the category and MCO column are in fact tables using RELATED function.

     

    Thanks

    Ruthwik

    • OwenAuger's avatar
      OwenAuger
      Super User
      4 years ago

      Hi again Ruthwik,

       

      If your fact tables are all related to Category & MCO dim tables, I would say it's best to avoid replicating those columns in the fact tables.

       

      Rather, within the role, I would suggest you create a RLS table filter expression repeated for each fact table, using RELATED function.

      I think it would look something like this:

       

      OR (
    RELATED ( DimCategory[Category] ) <> "Tea",
    RELATED ( DimMCO[MCO] ) IN { "India", "Nepal", "Indonesia" }
)

       

       Regards,

      Owen

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        OR(BPPlusReport[Category]<> "Tea",BPPlusReport[MCO] IN { "India", "Nepal", "Indonesia" }) this dax one is working in Power BI desktop but when i used this in Azure anlysis service (role)dax executed but when doing validate the role, it is not showing expected. Is there any limitation for above in AAS.

         

        Thanks

        Ruthwik