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.
I have this DAX Code: and it quadrupled my value, is there something wrong with double crossjoins?
Get Total Hours = SELECTCOLUMNS( FILTER( CROSSJOIN(table_c, FILTER( CROSSJOIN(table_a,table_b), table_a[employee_id] = table_b[employee_id] && table_a[start_date] >= table_b[date_effective_start] && table_a[start_date] <= table_b[date_effective_end] && table_a[start_date] >= table_b[date_production] ) ), table_a[str_buid_state_code] = table_c[str_buid_state_code] && table_a[start_date] >= table_c[date_effective_start] && table_a[start_date] <= table_c[date_effective_end] ), "Field Name 1", table_a[start_date], "Field Name 2", table_a[employee_id], "Field Name 3", table_b[supvsr_id], "Field Name 4", table_c[sys_description], "Field Name 5", table_a[duration], //this value Quadrupled "Field Name 6", table_c[flag_a], "Field Name 7", table_b[id_dept], "Field Name 8", table_b[id_role], "Field Name 1", table_b[flag_active_employee] )
Solved! Go to Solution.
There is nothing wrong with nested CROSS JOINs and they will work as intended.
I do recommend a slightly different technique for debugging.
I haven't changed any of your code, just broken out the tables so it's easier to see what is going on.
The final RETURN statement can be updated to be RETURN T1 or RETURN T2 etc so you can see how the data looks as you walk through the debugging.
So try this
Get Total Hours = Var T1 = FILTER( CROSSJOIN(table_a,table_b), table_a[employee_id] = table_b[employee_id] && table_a[start_date] >= table_b[date_effective_start] && table_a[start_date] <= table_b[date_effective_end] && table_a[start_date] >= table_b[date_production] ) ) Var T2 = FILTER( CROSSJOIN(table_c,T1 , table_a[str_buid_state_code] = table_c[str_buid_state_code] && table_a[start_date] >= table_c[date_effective_start] && table_a[start_date] <= table_c[date_effective_end] ) Var T3 = SELECTCOLUMNS(T2 , "Field Name 1", table_a[start_date], "Field Name 2", table_a[employee_id], "Field Name 3", table_b[supvsr_id], "Field Name 4", table_c[sys_description], "Field Name 5", table_a[duration], //this value Quadrupled "Field Name 6", table_c[flag_a], "Field Name 7", table_b[id_dept], "Field Name 8", table_b[id_role], "Field Name 1", table_b[flag_active_employee] ) Return T3
But I would probably create a SUMARIZE table of T2 which removes the duplicating durations.
There is nothing wrong with nested CROSS JOINs and they will work as intended.
I do recommend a slightly different technique for debugging.
I haven't changed any of your code, just broken out the tables so it's easier to see what is going on.
The final RETURN statement can be updated to be RETURN T1 or RETURN T2 etc so you can see how the data looks as you walk through the debugging.
So try this
Get Total Hours = Var T1 = FILTER( CROSSJOIN(table_a,table_b), table_a[employee_id] = table_b[employee_id] && table_a[start_date] >= table_b[date_effective_start] && table_a[start_date] <= table_b[date_effective_end] && table_a[start_date] >= table_b[date_production] ) ) Var T2 = FILTER( CROSSJOIN(table_c,T1 , table_a[str_buid_state_code] = table_c[str_buid_state_code] && table_a[start_date] >= table_c[date_effective_start] && table_a[start_date] <= table_c[date_effective_end] ) Var T3 = SELECTCOLUMNS(T2 , "Field Name 1", table_a[start_date], "Field Name 2", table_a[employee_id], "Field Name 3", table_b[supvsr_id], "Field Name 4", table_c[sys_description], "Field Name 5", table_a[duration], //this value Quadrupled "Field Name 6", table_c[flag_a], "Field Name 7", table_b[id_dept], "Field Name 8", table_b[id_role], "Field Name 1", table_b[flag_active_employee] ) Return T3
But I would probably create a SUMARIZE table of T2 which removes the duplicating durations.
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |