Creating a Matrix

[amehmood]

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.

[amehmood · Accepted solution]

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

from give Table 1

[v-yanjiang-msft · Accepted solution]

Hi @amehmood ,

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.

[daXtreme]

File attached

[amehmood]

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

[daXtreme]

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...

[amehmood · Accepted solution]

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

from give Table 1