I have a .pbix file using the pivot columns feature in Power Query and I cannot figure out how to handle potential future values. These are values that are not a part of the dataset just yet, and therefore cannot generate its own column from the pivot, but could potentially be a part of the dataset in the future.
For example. Consider the following dataset sample:
emplID, lastName, firstName, courseCode, attendance
000001, Doe, John, Training Course 1, Dropped from Class
000001, Doe, John, Training Course 2, Dropped from Class
000001, Doe, John, Training Course 3, Dropped from Class
Pivoted, this turns into:
emplID, lastName, firstName, Training Course 1, Training Course 2, Training Course 3
000001, Doe, John, Dropped from Class, Dropped from Class, Dropped from Class
My issue comes in for a future course, such as Training Course 4. Power Query should handle this just fine and add the appropriate column, but how can I perform a calculation on a potential column in DAX?
My visual is a multi-row card for Training Course 4. Among other things, it is counting how many students are dropped from Training Course 4. Without John Doe, or any student for that matter, being dropped from Training Course 4, I am not sure how to handle this in my visuals. I welcome any thoughts. I have manually set it to 0 for now, but this won't hold for the future.
p.s. I have to use the existing tables. I took over this .pbix file and I do not have the time to completely tear it apart and rebuild it.
