Forum Discussion
DAX: Create new column based on multiple conditions from other table
Hi all,
Unfortunately, I wasn't able to help myself with existing posts or the documentation, so trying my luck here.. :smileyhappy:
I have two main tables that have a relationship on the email
- Training enrolments (Course, Level, Status, Email)
- Learner list (Email)
Now for each of the courses and levels (e.g. course 1, level Foundation), I would to add a calculated column to the learner list, indicating whether he / she has completed this course (i.e. course = XYZ, Level = XYZ, Status = Completed, Email = Email). What is the most efficient / least performance-draining way doing so with DAX? Is there a better option than LOOKUPVALUE?
Any help is greatly appreciated!!
See if this helps. You need one calculated measure here to compute the status and then use a Matrix visual (add Email from the Learners List to the Rows, Course and New Status from Enrollments list to Columns, and the New Status to the Values section of the visual).
Make sure to Expand all down one level in the hierarchy in the Matrix visual, to see data both at course and foundation level.
Regards,
Tarun
7 Replies
- tarunsingla
Solution Sage
Why do you need a calculated column? If the two tables are related, you can add fields from both tables in any visual directly.
Trying to understand what calculation do you need to perform.
Hi tarunsingla, thanks a lot for your comment. I'm trying to create one column for each course & level (5 courses with 4 levels each from Foundation-Expert) where the value of the column is a True / False based on a few conditions from the enrolment table. The idea is to use these columns for implementing some belt certification logic using IF / OR conditions (e.g. if the learner completed course A on level Foundation and either course B or C on level Advanced, he gets certified as belt XYZ). Let me know if that makes sense. If not, I'll gladly upload some mock-up data. Cheers
- tarunsingla
Thanks for the explanation. It makes sense.
Since you mentioned about computing the certiciation logic at the learner level, that implies you would need to group/aggregate data at learner level. Calculated columns do not work best where aggregations are involved.
You would need to write a calculated measure instead and make use of SWITCH statement to incorporate multiple results based on various conditions.
https://docs.microsoft.com/en-us/dax/switch-function-dax
If you need more inputs on this, sample data and the logic to compute the status would help the community, to answer faster.