Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
3 years ago
Solved

Matrix table using multiple column

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...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    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
)
danextian's avatar
danextian
Super User
3 years ago

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's avatar
Saxon202202
Helper III
3 years ago

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's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      Saxon202202
      Helper III
      3 years ago

      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's avatar
        danextian
        Super User
        3 years ago

        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.