Forum Discussion

navin_Learning's avatar
navin_Learning
Frequent Visitor
3 years ago
Solved

DAX expression in creating role

Dear All,   So, i have two table   Policy table Policy RO Code SO code Sum insured 123 2 10 2000 124 3 20 3000 125 6 30 4000   User table User RO Code SO code ...
Need Help
  • RajaCSN's avatar
    RajaCSN
    3 years ago

    Hi navin_Learning,

     

    I have created a simple solution that will take care of your requirement using CALCULATETABLE function, which unfortunately will not work with Direct Query models.

     

    As long as you are going to use the import option (from any source), this method will work fine.

     

    As per sample data provide by you, the unfiltered table will look as below:

    As per your condition, user [email protected] can see only one record (ROCD=100, SOCD=20) and that happens.

    Another sample for user MNO is also shown below.

    You can see the same results for MNT also since his combination is also 101-30.

     

    I hope this is what you have described as your requirement and you can see it happening. 

     

    You can download the Excel and PBIX files from this link, if you want:

    RLS2-06-06-2023

     

    Please consider accepting this as a SOLUTION so that others can make a note of this and check when needed.  Your Kudos are also welcome.

     

    Best Regards,

     

    C.S.N. Raja

     

     

  • RajaCSN's avatar
    RajaCSN
    3 years ago

    1. In the source data you can type ALL for either ROCD or SOCD columns or for both the columns as the case may be.

     

     

    2. Scenario-1:  ROCD ALL BUT SOCD IS LIMITED TO 75

    3. Scenario-2: ROCD IS LIMITED TO 100 BUT SOCD IS ALL

     

    4. Scenario-3: ROCD = ALL & SOCD = ALL - Now all the records will be displayed

     

    5. Modified DAX code:

     

    6. Please note - both the tables are not connected. The ROCD & SOCD columns are converted into text, since I am using the "ALL" string in them.

     

    Hope this helps.

     

    Regards,

     

    C.S.N. Raja, MCT

    Power BI Trainer & Consultant

navin_Learning's avatar
navin_Learning
Frequent Visitor
3 years ago

Thank you dear Raja for your response, much appreciated!

I see that you have created relationship betweek ROCD and created role to filter the user.

 

Sorry not to mention it earlier, this is more complex

RO Code has multiple SO code, hence user should see data combining the RO & SO code.

Multiple user should see the same RO & SO code data

 

RO - regional code

SO - state code

So one state will have multiple people looking after the same data.

 

I  hope am able to explain my requirement, thank you for your help!

RajaCSN's avatar
RajaCSN
Advocate II
3 years ago

Creating RLS in complex scenarios is really interesting. Your requirement is pretty much possible to be implemented. 

 

Assume that I have a report similar to this image:

And a set of dynamic roles similar to this image:

I can limit the results as per combination of various fields (columns) for a given user. An example is shown below:  (All states, all categories but only consumer segment)

 

Sorry, unable to post my Excel, PBIX files here since they need to be santized before posting in a public domain. If you provide with me a skeleton dataset with your requirements, I shall create the roles and then share it with you.

 

Best Regards,

 

C.S.N. Raja