Conditional relationship for multiple tables

[Anonymous]

Hi, 

I have 4 tables 

logins, ConnectedUsers,FacilityCode, ProviderCode.   

Basically in ConnectedUsers table i have: 

Provider_Name	System_Code	Facility_Code	RegisteredUsers
AHSE	GAYA		10
CCMS	GAYA	AA02	4
AHSE	GAYA		6

logins

EventID	System_Code	Facility_Code
01	GAYA	AA02
02	GAYA

I have a measure that calculates logins based on event ID (count), however, I want the system to check if facility code in connectedusers table is blank then it should take system_code  registered users.

[lbendlin]

If you want to be cute you can rewrite

IF(ISBLANK( [Facility_Code]) ,[System_Code], [Facility_Code] )

as

COALESCE ( [Facility_Code]) ,[System_Code] )

but I'm not sure it would make any difference in performance.

[d_gosbell]

---

but I'm not sure it would make any difference in performance.

---

No, they are identical in terms of performance producing identical query plans. COALESCE is just syntactic sugar over the IF( ISBLANK() ...) format

[d_gosbell]

Well, I think you could do it in DAX by creating a calculated column in the connected users table something like:

IF(ISBLANK( [Facility_Code]) ,[System_Code], [Facility_Code] )

and then create your relationship on that column. But personally I would typically try and do row level calculations like this in PowerQuery or a source view if I can. It's not such a big deal on most tables if you are only dealing with a few million rows or less. See here https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/ for a discussion of the pro/cons of the two approaches and why it often does not matter.

[lbendlin]

You can't do that in DAX but you can merge tables in PowerQuery based on fuzzy logic like yours. Look at the custom Column Generator function in Table.AddColumn() .