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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jeronimo2334
Helper III
Helper III

Create new table applying cartesian logic

Hello all, I have 3 tables, 2 of them are dimensional:

DIM TABLE - EXAMS

exam1
exam2
exam3


DIM TABLE - STUDENTS

student1
student2
student3


FACT TABLE - RESULTS

exam2student1fail
exam2student1pass
exam3student1pass
exam1student2fail
exam2student2pass
exam2student3pass
exam3student3pass


I am trying to get a new table combining all these tables, it needs a row for each exam and each student and whether they pass, failed or not taken then exam.
Note, student1 failed exam2 on the 1st row, but then passed it on the 2nd row. The expected table should only show the passed result.


EXPECTED TABLE

exam1student1not taken
exam2student1pass
exam3student1pass
exam1student2fail
exam2student2pass
exam3student2not taken
exam1student3not taken
exam2student3pass
exam3student3pass

 

Any help is appreciated.
Thanks.

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

@jeronimo2334  Updated to meet that additional requirement

 

Place the following M code in a blank query to see the steps for your expected table. 

See it all at work in the attached file.

 

let
    Source = ExamsT,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each StudentsT),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student"}, {"Student"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Student", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Exam", "Student"}, ResultsT, {"Exam", "Student"}, "ResultsT", JoinKind.LeftOuter),
    #"Expanded ResultsT" = Table.ExpandTableColumn(#"Merged Queries", "ResultsT", {"Result"}, {"Result"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded ResultsT",null,"Not taken",Replacer.ReplaceValue,{"Result"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Value",{{"Exam", Order.Ascending}, {"Student", Order.Ascending}, {"Result", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Exam", "Student"})
in
    #"Removed Duplicates"

 

AlB_0-1671827317268.png

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

8 REPLIES 8
AlB
Community Champion
Community Champion

Hi @jeronimo2334 

Place the following M code in a blank query to see the steps for your expected table. 

See it all at work in the attached file.

let
    Source = ExamsT,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each StudentsT),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student"}, {"Student"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Student", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Exam", "Student"}, ResultsT, {"Exam", "Student"}, "ResultsT", JoinKind.LeftOuter),
    #"Expanded ResultsT" = Table.ExpandTableColumn(#"Merged Queries", "ResultsT", {"Result"}, {"Result"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded ResultsT",null,"Not taken",Replacer.ReplaceValue,{"Result"})
in
    #"Replaced Value"

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Thank you! That is almost the expected result. I edited my post, adding a small gotcha.
Issue being, a student can have multiple entries in the Results table, with a fail and a pass grade. The expected table should show only the pass if there is a case of multiple results. It should only show the fail result if there is no pass result.

Simple enough

CNENFRNL_0-1671832806540.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

AlB
Community Champion
Community Champion

@jeronimo2334

Can the results only be pass or fail?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Correct.

AlB
Community Champion
Community Champion

@jeronimo2334  Updated to meet that additional requirement

 

Place the following M code in a blank query to see the steps for your expected table. 

See it all at work in the attached file.

 

let
    Source = ExamsT,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each StudentsT),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student"}, {"Student"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Student", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Exam", "Student"}, ResultsT, {"Exam", "Student"}, "ResultsT", JoinKind.LeftOuter),
    #"Expanded ResultsT" = Table.ExpandTableColumn(#"Merged Queries", "ResultsT", {"Result"}, {"Result"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded ResultsT",null,"Not taken",Replacer.ReplaceValue,{"Result"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Value",{{"Exam", Order.Ascending}, {"Student", Order.Ascending}, {"Result", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Exam", "Student"})
in
    #"Removed Duplicates"

 

AlB_0-1671827317268.png

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Thank you that worked. The pbix seems to be from some other post but the M query is right.

AlB
Community Champion
Community Champion

@jeronimo2334 

Corrected the pbix

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.