DAX: Create new column based on multiple conditions from other table

[Anonymous]

Hi all,

Unfortunately, I wasn't able to help myself with existing posts or the documentation, so trying my luck here.. 

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!!

[tarunsingla]

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.

[Anonymous]

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.

[Anonymous]

Thanks for your fast response @tarunsingla !

The data looks conceptually as follows:

1. Learners table

Email	Course A - Foundation	Course A - Intermediate
Learner1@XYZ.com	Completed	Not completed
Learner2@XYZ.com	Completed	Completed
Learner3@XYZ.com	Not completed	Not completed

2. Enrolment table

Course	Level	Email	Status
Course A	Foundation	Learner1@XYZ.com	Completed
Course A	Foundation	Learner2@XYZ.com	Completed
Course A	Intermediate	Learner2@XYZ.com	Completed

And I'd like to calculate the fields in amber, by checking if there is a completed record for the respective course and level in the other table. So with a measure, should it look something like to following for the Course A Foundation column:

CHECK_A_Found = SWITCH( TRUE(), AND(Course = "Course A"; Level = "Foundation"; Status = "Completed"); "Completed"; "Not Completed")

Thanks a ton for your help, really appreciated!

[tarunsingla · Accepted solution]

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

[Anonymous]

Hi @tarunsingla , that's already super helpful as an intermediate step. Now I need to be able to implement the certification logic based on a few IF / OR conditions.

E.g. Certification A is passed if (Course A Foundation and Course B Foundation) or (Course A Foundation and Course C Foundation) are passed.

So I pretty much need the values of the completed modules by learner to creat an additional column which says if a specific certification is passed based on the conditions. What would be the most efficient way to do this? 

Thanks again!

[Anonymous]

Short update: Managed to solve it now with a combination of LOOKUPVALUE() and SWITCH() statements and a few additional calculated columns in the learner table. So I have basically rebuilt your above visual, @tarunsingla , with columns of 1s / 0s. Still using your solution with a UNICHAR tickmark for illustration purposes, though. So thanks a ton again!!