Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WunWun
Frequent Visitor

Pivoted Data and Potential Future Values

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.

3 REPLIES 3
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors