Forum Discussion

meghansh's avatar
meghansh
Frequent Visitor
1 year ago
Solved

Many to Many relationship

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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    lbendlin
    Super User
    1 year ago

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