Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
I have a .pbix file using the pivot columns feature in Power Query
Don't do that. You get the pivoting for free in the Power BI visuals.
While I do agree, using the pivot column feature is bad practice, as I mentioned at the end of my question, I took this over from someone else and do not have the time to pull this whole thing apart and rebuild it.
So I am stuck with this for now, but my problem still stands. How can I handle future columns and perform DAX calculations using them before they are available?
You cannot. Power BI is fundamentally incapable of doing that.
Spend the effort to rebuild.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |