topic Re: Conditional relationship for multiple tables in Report Server

Conditional relationship for multiple tables

Anonymous — Thu, 04 Feb 2021 08:57:47 GMT

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.

Re: Conditional relationship for multiple tables

lbendlin — Thu, 04 Feb 2021 18:27:34 GMT

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() .

Re: Conditional relationship for multiple tables

d_gosbell — Sun, 07 Feb 2021 23:16:02 GMT

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.

Re: Conditional relationship for multiple tables

lbendlin — Mon, 08 Feb 2021 03:36:09 GMT

If you want to be cute you can rewrite

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

COALESCE ( [Facility_Code]) ,[System_Code] )

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

Re: Conditional relationship for multiple tables

d_gosbell — Mon, 08 Feb 2021 08:00:43 GMT

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