Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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.
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)
@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.
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.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |