Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
exam2 | student1 | fail |
exam2 | student1 | pass |
exam3 | student1 | pass |
exam1 | student2 | fail |
exam2 | student2 | pass |
exam2 | student3 | pass |
exam3 | student3 | pass |
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
exam1 | student1 | not taken |
exam2 | student1 | pass |
exam3 | student1 | pass |
exam1 | student2 | fail |
exam2 | student2 | pass |
exam3 | student2 | not taken |
exam1 | student3 | not taken |
exam2 | student3 | pass |
exam3 | student3 | pass |
Any help is appreciated.
Thanks.
Solved! Go to Solution.
@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"
|
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. |
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"
|
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
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! |
Can the results only be pass or fail?
|
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.
@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"
|
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |