Need alternative to USERELATIONSHIP for RLS

[Wayfarer]

Hi.

I need some advice or pointers on how I can solve a problem I now have.  Frustration is mounting 😞
I have been using USERELATIONSHIP successfully in a measure that utilises an inactive relationship.  The numbers are good, report looks great.  Except, I also need to use RLS, which USERELATIONSHIP does not support.  I must have missed that when implemented it in the first place.

I've browsed several posts on here and elsewhere, and I've not been able to make it work.  The solutions are confusing or I haven't successfully adapted it to my report and data model.

https://www.daxpatterns.com/handling-different-granularities/

https://community.powerbi.com/t5/Desktop/Alternative-to-UserRelationship/m-p/641982

https://community.powerbi.com/t5/Desktop/RLS-with-Roleplaying/m-p/707605

https://community.powerbi.com/t5/Desktop/RLS-not-working-inactive-relation-and-measure/m-p/350464

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP-and-RLS/m-p/787463

An abstracted overview of my scenario:

• A fact table of Job details.
• A dimension table for Company.  This has RLS on it.
• Job relates to Company on 2 columns:  job owner and job performer.
• I have several page-level filters.  I have slicers for date and job owner company.  Whatever calculations I do need to respect the page-level filters and any selections in the slicers.

Fact table [Job details]		Dimension table [Company] [has RLS]
JobID [used for count]		CompanyKey
CompanyKeyOwner	CompanyKey [M <--> 1] CompanyKey
CompanyKeyPerformer	CompanyKey [M <--> 1] CompanyKey

What I need the report to do:

• From a slicer, select a company.
  This is the company that "owns" the job.
• Show a count of how many jobs that company has, by the "performer" company.
  For example, Company A might have 20 jobs done by Company B; and 10 jobs done by Company C.
  This one's easy peasy, I have that working.
• The bit I can't do due to RLS is the reverse of that.  Show a count of how many jobs that Company A has done for anyone else.
  For example, show that Company A has done 5 jobs for Company B; and 0 for Company C.

With Company A as "owner" selected in the slicer, it filters the whole fact table by the CompanyKeyOwner column.  I need to be able to apply that filter to the CompanyKeyPerformer column instead.  The data is all in the same table, I just want to filter by the same value but on a different column.  Is it not as simple as it seems?

I can do this no problem in SQL; select from table where CompanyKey is in Owner column; select from table where CompanyKey is in Performer column.  Can't get my head around how to translate it to DAX functionality.

What are my options?

Duplicate either or both tables?  Not sure how that'd help when I need to do aggregations and measures.

Some kind of FILTER expression a bit like in https://www.daxpatterns.com/handling-different-granularities/ ?

[Villezekeviking]

Here's a solution where you no longer need multiple relationships 🙂

https://www.villezekeviking.com/alternative-to-userelationship/

[Wayfarer]

I wouldn't call it a particularly satisfactory solution, but I've got something working.  Might help someone else who comes across a similar problem.

If I duplicate both the fact and dimension tables and set the relationships the right way for the situation (i.e. the reverse of the existing relationship), I can use a simple count on the id field in the visual.  No crazy measure code required.

I just don't like having to have copies of the same table, but it is what it is.  It means I also have to duplicate some of the page-level filters, or apply specific filters in the power query layer.