Forum Discussion
Unpivot calculated columns using DAX query
Hi, I have the following columns User Critical Use 1 Critical Use 2 Critical USe 3 Critical Use Critical Use Calc Creative 1 Creative 2 Creative_Calc Creative ABC 40 40 30 3...
The below is the original data:
| User | Response 1 |
Response 2 | Response 3 | Response 4 | Response 5 |
| ABC | Adopting | Performing | Adopting | Leading | Experienced |
| DEF | Adopting | Leading | Performing | Adopting | Performing |
| GHI | Performing | Experienced | Leading | Performing | Adopting |
I have added some calculated columns to the above table:
| User | Response 1 | Response 2 | Response 3 | Response 4 | Response 5 | Critical Use 1 | Critical Use 2 | Critical USe 3 | Critical Use | Critical Use Calc | Creative 1 | Creative 2 | Creative_Calc | Creative |
| ABC | Adopting | Performing | Adopting | Leading | Experienced | 40 | 30 | 40 | 36.67 | Adopting | 60 | 80 | 70 | Adopting |
| DEF | Adopting | Leading | Performing | Adopting | Performing | 40 | 60 | 30 | 43.33 | Exploring | 40 | 30 | 35 | Performing |
| GHI | Performing | Experienced | Leading | Performing | Adopting | 30 | 80 | 60 | 56.67 | Exploring | 30 | 80 | 55 | Adopting
|
These calculations are based on the below lookup tables:
| Adopting | 40 |
| Leading | 60 |
| Performing | 30 |
| Experienced | 80 |
| Adopting | 36.67 |
| Exploring | 43.37 |
| Exploring | 56.67 |
| Adopting | 55 |
| Adopting | 70 |
| Performing | 35 |
I now need to get the table to pivot the data as follows:
| ABC | Critical Use 1 | 40 |
| ABC | Critical Use 2 | 40 |
| ABC | Critical USe 3 | 30 |
| ABC | Critical Use | 33.6 |
| ABC | Critical Use Calc | Adopting |
| ABC | Creative 1 | 40 |
| ABC | Creative 2 | 60 |
| ABC | Creative_Calc | 50 |
| ABC | Creative | Adopting |
I cannot use the transform function in Query editor because the calculated columns wouldnt appear there. What would be an alternative way to get this displayed?