Pivot the column which change values dynamically in Direct query mode

[Anonymous]

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.

SQL database table

Question   Answer

ABC            yes 

DEF            No

GHI            Taken

Final result

ABC        DEF        GHI

yes          No         Token

If the values change dynamically as following-

Question   Answer

ABC            yes 

DEF            Yes

GHI            Taken

JKL             Registered

Final result should be-

ABC        DEF         GHI               JKL

yes          Yes         Token           Registered

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. 

Also the requirement is to use 'Personalize visual' , so that user can select any question on any visual.

[selimovd]

Hey @Anonymous ,

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:

The alternative is to ask if you really need DQ as it has a lot of disadvantages.

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 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks for the reply @selimovd . 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)

[amitchandak]

@Anonymous , pivot is not supported in direct query

Create a measure

M1= max(Table[Answer])

In matrix visual use Question on Column, Nothing on row, M1 as Value,

If need switch on- Show on row.

[Anonymous]

Thanks for the reply @amitchandak . 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.