Reading more than one Value in a Row and Column

[Anonymous]

Hello and good morning!

I've brainstormed this but haven't found a solution so far! So I'm coming to you for help. Heres the situation:

I have a dataset of accidents, with many infos on each specific accident. My issue here regards the vehicles involved in this accident.

In my data, I have a column like this:

Local	Veículos
Faixa 2	3:Automóvel(FIAT / PALIO);2:Perua/Caminhonete(VW / SAVEIRO);1:Caminhão(MB) ;

"This a cut of the data, can't use it all because of sensitive info"

In the column Veículos, I can have from 1 to 10 (or more) vehicles involved in the accident, and I only need the type of vehicle involved (ex: Automóvel; Perua/Caminhonete; Caminhão and this can easily be done with some data work).

But I couldn't workout how to make a graph with all these types in the same column,

so I deleted all vehicles but the first one (which is the one that causes the accident). This led me to a problem, where the number of vehicles involved in accidents is the same number of total accidents, and this is untrue.

The thing is, I could split each type of vehicle in different columns, but I wouldn't be able to "count" each of them and display them just like in the graph above (or would I? I dunno how). I was wondering if there was a way to create a "List" on the row values, in a single column, where Power BI would read the List and interpret as {"Automóvel","Perua/Caminhonete","Caminhão"} (as the example) and understand that it means to count them as individuals values.

If this question wasn't clear enough, let me know so I can try to explain better

Thanks in advance!

[DataZoe · Accepted solution]

@Anonymous What @Greg_Deckler says is correct, but I can give you a few more insights on how to do that:

In the Transform Data, I would follow these steps:

1. Right-click your table, and choose "Duplicate"

2. In the new table, click the top right and then choose columns

3. Choose the vehicles and an identifier to the first table (such as AccidentID or something).

4. Click on the column with the multiple vehicles and then go to Split Column --> By Delimiter

5. On the pop up, change it to by semi-colon and then open Advanced and choose by rows

6. Now I would remove blanks by clicking on the drop down arrow and choosing "Remove Empty"

7. Now we can use that split columns again to remove the id from the name.

Now the table is expanded by vehicle, you join it back to the main table by relationship:

And use that new table's list of vehicles in your treemap visual:

Hope that helps!

[Greg_Deckler]

@Anonymous Can you split them out and then unpivot them in Power Query? Since you have a dynamic number of columns, you would select the columns you didn't want to unpivot and then right-click, Unpivot other columns.

[Anonymous]

 @Greg_Deckler I could and it would work, but It would mean 3-6x more rows in my dataset, which is already big (~800.000 rows), plus I would have change all the DAX I've written so far (cause they weren't written for duplicate rows, different only on the vehicles) and I would like to avoid doing that and maintain one row per accident, it would means a whole lot of re-work. I'm trying to find better ways of doing this

[DataZoe]

@Anonymous To you concerns, if you follow the steps I have provided it won't create additional rows in your main table, just the second one with limited data, and would not be a big impact to performance. You would also not have to rebuild your existing measures.

[Anonymous]

@DataZoe I just read it, I didn't think of that! Creating a duplicated "dimension" table only for the Vehicles could work. Excited to try it, will return to you after.

Edit: @DataZoe  It worked perfectly! Had to do some fine adjustments to the data, and had to enable a "two-way" relationship for it to work as an filter as well. Thank you for your time!