Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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() .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
6 | |
4 | |
3 | |
3 |
User | Count |
---|---|
15 | |
9 | |
6 | |
3 | |
3 |