Trouble using DAX to calculate properly between multiple active/inactive relationships

wboyle — Thu, 15 Feb 2024 21:38:41 GMT

Hello,

I'm trying to understand if leveraging the Power BI relationships is a good place to do this, or if it's better suited to something like Power Query, or if we just write a database query that gives the proper output. We've been trying to get away for having so many custom views in the database so I'm trying with in app relationships first.

In my scenario, the data source is a relational database with several tables. We have unique IDs tied to individual users, and there is a People Table that holds all of the user information such as their Names. I have to retrieve the names for multiple fields, which means Power BI needs a mix of active and inactive relationships established. Screenshots of the relationships are at the bottom of the post.

Another workaround we thought of that I don't like is having multiple/separate queries pull the ID and Name from the people table and we have an active relationship and a new query for every named individual I'm trying to surface.

The main active relationship I started with was joining 'report er_log' [lead_project_manger_id] to 'report people' [id]. I'm using a simple IF statement that successfully returns the Lead Project Manager name when those two IDs match - success!

Lead PM Name = IF('report er_log'[lead_project_manager_id]=RELATED('report people'[id]),RELATED('report people'[full_name]))

The issue comes in for the inactive relationship between 'report activity' [record_owner_id] to 'report people' [id]. Any formula I've used that didn't throw a relationship error will ignore the inactive relationship entirely and just return the PM Name.

e.g.

my latest attempt was using LOOKUPVALUE. my understanding is that LOOKUPVALUE should ignore relationships but it definitely doesnt and just gives me PM names and not all record owners
- LOOKUPVALUE('report people'[display_name],'report people'[id],'report activity'[record_owner_id])

My first attempt was using a simple IF statement. this also just returns PM names and has issues because RELATED only likes the active relationships
- IF('report activity'[record_owner_id]=RELATED('report people'[id]),RELATED('report people'[full_name]))

The only thing i found that might work for the USERLATIONSHIP function throws an error because in this context there's no active relationship with report people
- CALCULATE(RELATED('report people'[display name]),USERELATIONSHIP('report activity'[record_owner_id],'report people'[id]))

Any ideas are appreciated. I'm starting to play with Power Query, and worst case I think my workaround will be fine... I just don't want to have so many distinct queries going to report people if we can help it.

EDIT: I tried the workaround for funsies and it seems like the same relationship issue with the original people table is still causing heartache.

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships

lbendlin — Fri, 16 Feb 2024 02:41:29 GMT

Have you considered refactoring your data model and using RLS ?

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships

wboyle — Fri, 16 Feb 2024 17:33:24 GMT

Yeah I'm leaning towards in app relationships are the wrong solution for this. Changing the data model outside of a custom query or database view won't happen with this system due to politics and reasons.

RLS meaning row level security? I haven't looked into that much myself but a team member has, I'll make a note to ask.

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships

wboyle — Mon, 19 Feb 2024 16:42:53 GMT

Closing the loop in case anyone comes across this thread with a similar issue. I ended up solving the in-app problem by making all of the relationships for people names inactive. This made the LOOKUP formula return proper results.

topic Trouble using DAX to calculate properly between multiple active/inactive relationships in DAX Commands and Tips

Trouble using DAX to calculate properly between multiple active/inactive relationships

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships

Re: Trouble using DAX to calculate properly between multiple active/inactive relationships