Forum Discussion
Conditional relationship for multiple tables
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.
4 Replies
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.
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
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.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() .
