Pivot the column which change values dynamically in Direct query mode

Question

I want to pivot the column which changes data dynamically and use the same in power BI desktop , by using Direct query mode so as to refresh data immediatly.&nbsp;SQL database tableQuestion&nbsp; &nbsp;AnswerABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; yes&nbsp;DEF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NoGHI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Taken&nbsp;Final resultABC&nbsp; &nbsp; &nbsp; &nbsp; DEF&nbsp; &nbsp; &nbsp; &nbsp; GHIyes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Token&nbsp;If the values change dynamically as following-Question&nbsp; &nbsp;AnswerABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; yes&nbsp;DEF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YesGHI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TakenJKL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Registered&nbsp;Final result should be-ABC&nbsp; &nbsp; &nbsp; &nbsp; DEF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GHI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;JKLyes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Yes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Token&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Registered&nbsp;As pivot doesn't support in direct query mode in power BI desktop, please suggest some way to achieve the row values as columns and use in power BI desktop in direct mode.&nbsp;Also the requirement is to use 'Personalize visual' , so that user can select any question on any visual.

amitchandak · Answer

Anonymous , pivot is not supported in direct query 
Create a measure
M1= max(Table[Answer])
&nbsp;
In matrix visual use Question on Column, Nothing on row, M1 as Value,
&nbsp;
If need switch on- Show on row.

selimovd · Answer

Hey&nbsp;Anonymous&nbsp;,&nbsp;as you said, pivot is not supported in DQ.What you can do is to use the Matrix visual and take the Question in the columns and Answer in values. Like this you will get a matrix like that:&nbsp;&nbsp;&nbsp;The alternative is to ask if you really need DQ as it has a lot of disadvantages.&nbsp;If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍&nbsp;Best regardsDenis&nbsp;Blog: WhatTheFact.biFollow me: twitter.com/DenSelimovic

anonymous · Answer

Thanks for the reply selimovd&nbsp;. Yes, matrix visual can be used when the visual is stable. But in my case, requirement is to use 'personalize visual' option. So it will be confusing for user to select different questions when required. (updating the question with the same)

anonymous · Answer

Thanks for the reply amitchandak&nbsp;. Yes, matrix visual can be used when the visual is stable. But in my case, requirement is to use 'personalize visual' option. So it will be confusing for user to select different questions when required. (updating the question with the same). Also,this measure did not make much difference in the matrix.&nbsp;&nbsp;&nbsp;

Forum Discussion

Pivot the column which change values dynamically in Direct query mode

4 Replies

Related content

Power BI - Dynamically Change Column Name

Change column types dynamically

Dynamic Column headers

Dynamic Column Name

Dynamic Variable in Calculated Columns