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 | 33.6 | Adopting | 40 | 60 | 50 | Adopting |
| DEF | 40 | 60 | 40 | 46.67 | Exploring | 60 | 60 | 60 | Performing |
| GHI | 40 | 20 | 20 | 26.67 | Exploring | 20 | 60 | 40 | Adopting |
The columns Critical Use 1, Critical Use 2, Critical USe 3, Critical Use Calc, Critical Use, Creative 1, Creative 2, Creative_Calc, Creative are all calculated columns.
How can I get a resultant tableto pivot the data above?
| 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 |
6 Replies
could you pls provide some sample data?
Please see example below:
Response 1, 2, 3, 4 and 5 are original columns. The remaining columns are calculated columns. I would like to pivot the data using the calculated columns to obtaing the resultant 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 40 30 33.6 Adopting 40 60 50 Adopting DEF Adopting Leading Performing Adopting Performing 40 60 40 46.67 Exploring 60 60 60 Performing GHI Performing Experienced Leading Performing Adopting
40 20 20 26.67 Exploring 20 60 40 Adopting
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?
