Many to Many relationship

[meghansh]

i have 2 tables as Phase and Course. Course has following fields and values

CourseID = 1, phase = basic, start date = 24 aug, 2024, end date = 30 aug 2024

CourseID= 1, phase = intermediate, start date = 1 sept 2024, end date = 30 sept 2024.

CourseID= 1, phase = advanced, start date = 1 oct 2024, end date = 30 oct 2024.

Phase has

Phase ID = 1, CourseID = 1, phase = basic, comment = abc,

Phase ID = 2, CourseID = 1, phase = intermediate, comment = txt,

now as i join these tables in power bi, evidently this create a many to many relationship.

what i need in the report view is a table which says,

CourseID= 1, phase = basic, start date = 24 aug 2024, end date = 30 aug 2024, comment = abc

CourseID= 1, phase = intermediate, start date = 1 sept2024, end date = 30 sept2024, comment = txt.

What can i use to achieve this? Thanks.

[Anonymous · Accepted solution]

Hi @meghansh 

Please try this:

Here's the sample data:

Then add a calculated table:

Table = 
	SELECTCOLUMNS(
		FILTER(
			CROSSJOIN(
				'Course',
				SELECTCOLUMNS(
					'Phase',
					"_phase", 'Phase'[phase],
					"_CourseID", 'Phase'[CourseID],
					'Phase'[comment]
				)
			),
			'Course'[CourseID] = [_CourseID] && 'Course'[phase] = [_phase]
		),
		'Course'[CourseID],
		'Course'[phase],
		'Course'[start date],
		'Course'[end date],
		'Phase'[comment]
	)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

your sample data does not demonstrate the need for a many to many relationship.  Provide sample data with more course IDs, for example.