Matrix table using multiple column

[Saxon202202]

Hi,

I have data data table and the table contain country id, country code and qty.

In visualisation, I like to breakdown by country id, country code and qty in matrix table.
At the moment all the country code showing in one column by country id but the expected result is one by one, row by column (country Id by country code). Is that possible to achieve in visuslation?

Example: Return the releevant country code in each columns. If country code - RS in row and column will be show only RS country code.

https://www.dropbox.com/s/18q4wdjaszlml3x/PBI%20VISUAL%20-MATRIX.pbix?dl=0

[danextian]

Hi @Saxon202202,

If I got your user case right, my approach would be to create an index column for each Country ID, use that as the column header and use SELECTEDVALUE to show the country code in the row. This is the closest I could get to. Please refer to the attached PBIX

[Saxon202202]

Hello, Thanks for your suggestion. The country id is not part of raw data, it's came from DAX code based on the different columns so unable to modify the changes in Power query. Is that any chance can I have the same index output by using DAX code. I can get the same output in Excel by using COUNTIFS =COUNTIFS(A2:A$2,A2) but not sure how can make DAX code to get the same output. 

[danextian · Accepted solution]

Hi @Saxon202202 

I've edited the PBIX with a calculated column to rank Country Code by Country ID.

Index = 
RANKX (
    FILTER ( DATA, DATA[COUNTRY ID] = EARLIER ( DATA[COUNTRY ID] ) ),
    DATA[COUNTRY CODE],
    ,
    ASC,
    DENSE
)

[Saxon202202]

@danextian 
Thank you so much for your help to make DAX code for Index. The last question is how do you made visual because I am unable to get the same visual output like you made it.
Can you please advise. 
My visual is different compare to yours.

[danextian]

Hi @Saxon202202 ,

Change the column font color to white so they are not visible (with a white background of course).  Then under Values, turn on Switch values to rows.

[Saxon202202]

@danextian , Thank you so much for your quick respones and help.

[Dinesh_Suranga]

@Saxon202202 

Hi,

Could you please let me know your COUNTRY ID and COUNTRY CODE static or dynamic ?

Thank you.

[Saxon202202]

@Dinesh_Suranga, Thanks for your response. Tge both columns are static. 

[Anonymous]

Hi  ,

This can be resolved using switch function

SWITCH = 

SWITCH(
    TRUE(),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "RS",SELECTEDVALUE(DATA[COUNTRY CODE]),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "PD",SELECTEDVALUE(DATA[COUNTRY CODE]),
    SELECTEDVALUE(DATA[COUNTRY ID]) = "OQ",SUM(DATA[ORDER QTY])
)

BR

@Saxon202202

[Saxon202202]

@Anonymous @, Thanks for your reply. The country id should be show the relevant country code.