Forum Discussion
Post ProdigySWITCH ROW TO COLUMN IN POWER BI VISULATION
Hi,
I changed the data from row to column in Power BI visualization but the country still showing on the Legend. I would like to show the report in Power BI visualization by country with status not status by county.
Actual Data:
| COUNTRY | UK | US | INDIA | SA | SRL | PAK | BAN | AFG | ENG |
| MATCHED | 14 | 228581 | 219077 | 282124 | 284406 | 216292 | 179342 | 193704 | 216 |
| NOT MATCHED | 56133 | 65637 | 2590 | 308 | 68422 | 105372 | 91010 | 98 | |
| NOT REQUIRED | 284698 | 284398 | |||||||
| NOT MATCHED | 56133 | 65637 | 2590 | 308 | 68422 | 105372 | 91010 | 98 |
Actual Power BI data report
Desired Result
Hello @Saxon10 ,
Depending on your description, you can create a calculated table as follows.
Table_2 - UNION(
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","UK","Value",'Table_1'[UK]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","US","Value",'Table_1'[US]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","INDIA","Value",'Table_1'[INDIA]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SA","Value",'Table_1'[SA]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SRL","Value",'Table_1'[SRL]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","PAK","Value",'Table_1'[PAK]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","BAN","Value",'Table_1'[BAN]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","AFG","Value",'Table_1'[AFG]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","ENG","Value",'Table_1'[ENG])
)Result:
I hope that's what you were looking for.
Best regards
Yuna
If this post helps,then consider Accepting it as the solution to help other members find it faster.
6 Replies
- amitchandak
Super User
Saxon10 , Unpivot this data, You will get country and status as columns. ANd value as one more column (numeric
In matrix you can status as row, Country as column and Value in Value .
Saxon10thanks for quick reply. I can't use unpivot option because the column came from DAX measure not part of the original data. Could you please advise is there any alternative way?
- amitchandak
Saxon10 , are UK, US are measures then how orginal data looks like ?
- Saxon10
The 9 column are came from multiple dataset some of the theme numbers columns and some of the column are text and numbers. it's very large database.
Hello @Saxon10 ,
Depending on your description, you can create a calculated table as follows.
Table_2 - UNION(
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","UK","Value",'Table_1'[UK]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","US","Value",'Table_1'[US]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","INDIA","Value",'Table_1'[INDIA]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SA","Value",'Table_1'[SA]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SRL","Value",'Table_1'[SRL]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","PAK","Value",'Table_1'[PAK]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","BAN","Value",'Table_1'[BAN]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","AFG","Value",'Table_1'[AFG]),
SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","ENG","Value",'Table_1'[ENG])
)Result:
I hope that's what you were looking for.
Best regards
Yuna
If this post helps,then consider Accepting it as the solution to help other members find it faster.
- Saxon10
Thanks for your time. This is exactly I am looking for to achieve my desired result.
