Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Creating a Matrix

Hi

 

 

How to make Table 2 based on data in Table 1. Table 1 contains accident data, Crash ID refers to a unique ID of a crash, Veh Type refers to the types of vehicles involved in crashes, and the Driver column contains the driver either responsible or the victim. For instance, crash Id 100210000000 occurred between two cars and one of them was responsible.

 

I tried the matrix visual option and created two columns as follows.

DrRes = if([Driver]= “Responsible” , [Veh Type], blank ())

DrVic = if([Driver]= “Victim” , [Veh Type], blank ())

 

And used DrRes as Rows and DrVic as column but no luck to create desired Table 2, please assist.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks for your kind assistance, could I ask how to create the following table

     

     

    from give Table 1

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Select the Driver column, then click Pivot Column.

    Select "Veh Type" column in the Values Column option and select Don't Aggregate in the Advanced options.

    Result:

    2. In a matrix visual, put Responsible column in Rows, Victim column in Columns, Crash ID column in Values and select Count in the dropdown option, get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your kind assistance, could I ask how to create the following table

       

       

      from give Table 1

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you very much DaXtreme very helpful, I ran to another issue: ole db or odbc error

      • daXtreme's avatar
        daXtreme
        Solution Sage
        3 years ago

        Everything that's needed to create the matrix I created in the file is in the file. Even the transformations needed to create the table in the model from the initial one...

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Select the Driver column, then click Pivot Column.

    Select "Veh Type" column in the Values Column option and select Don't Aggregate in the Advanced options.

    Result:

    2. In a matrix visual, put Responsible column in Rows, Victim column in Columns, Crash ID column in Values and select Count in the dropdown option, get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.