DAX expression in creating role

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

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