Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Nested Crossjoin Quadrupled my Data

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]
			)

 

2 ACCEPTED SOLUTIONS
Phil_Seamark
Microsoft Employee
Microsoft Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

But I would probably create a SUMARIZE table of T2 which removes the duplicating durations.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Microsoft Employee
Microsoft Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

But I would probably create a SUMARIZE table of T2 which removes the duplicating durations.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

,

Im mobile right now, Im excited to test out your recommendations. I'll give feedback after performing those. BiG thanks, learned so much from you already from previous posts including this!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors