Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I would like to have Column header to be displayed in First Column and its values to be displayed in second column.
We can do transpose in Edit Queries but i want to change my visualization part alone.
Thanks in Advance
Solved! Go to Solution.
Hi @Anonymous ,
Then you need first create a table ,and put all the column headers into one column,such as below:
Then you need a measure as below:
Measure =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Address", SELECTEDVALUE ( 'Table (2)'[Address] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Amount", SELECTEDVALUE ( 'Table (2)'[Amount] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Country", SELECTEDVALUE ( 'Table (2)'[Country] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Gender", SELECTEDVALUE ( 'Table (2)'[Gender] )
)
Finally in the new table ,put column and measure into a matrix as below:
And you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
Hi @Anonymous
you can use the next technique with creating new calculated table. the exactly solution depends on your data model and aggregation business logic
Table2 =
UNION(
ROW("Field"; "Amount"; "Value"; SUM(Table1[Amount]));
ROW("Field"; "Address"; "Value"; FIRSTNONBLANK('Table1'[Address];1));
ROW("Field"; "Country"; "Value"; FIRSTNONBLANK('Table1'[Country];1))
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @az38 ,
I tried your suggestion but i am not getting it properly. Can you explain it in more detaild manner. I assume i am getting confused by the delimiters present in the code which you sent. ( ; , )
Thanks
@Anonymous
delimiter depends on your system localization settings.
you can just to replace ";" to "," in my statement
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @az38 ,
It is almost working but when i select anything in filter then because of firstnonblank function it is not assciating properly.
Thanks
In matrix visualization, you have the option Show on Row. That will move you measure on the row.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi @amitchandak ,
Can you explain in more detail on hoe to get that in matrix.
Original dataset
Expected Output
Thanks
Hi @Anonymous ,
Go to "edit queries">select all columns>"Transform">"Unpivot columns":
Then choose "Use first row as headers":
Choose "close and apply",then you will see :
Best Regards,
Kelly
Thanks for your response but do we have any other way to do it without using Query editor and Edit Queries section. i.e., in front end itself or just creating a measure or calculated column?
Thanks
Hi @Anonymous ,
Then you need first create a table ,and put all the column headers into one column,such as below:
Then you need a measure as below:
Measure =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Address", SELECTEDVALUE ( 'Table (2)'[Address] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Amount", SELECTEDVALUE ( 'Table (2)'[Amount] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Country", SELECTEDVALUE ( 'Table (2)'[Country] ),
SELECTEDVALUE ( 'Table (4)'[Header] ) = "Gender", SELECTEDVALUE ( 'Table (2)'[Gender] )
)
Finally in the new table ,put column and measure into a matrix as below:
And you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
Hi @v-kelly-msft ,
Let us assume out of these 4 column we have 1 as a measure then how to give that measure value?
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |