Using two sets of slicers with AND condition

[Anonymous]

I am beginner. I have started a Power BI project with error codes in Vehicles.

I have imported an Excel sheet with 200.000 rows of various Error Codes for some vehicles, using one Table.

An Error Code is "ECU" and "DTC Code" combined, (example  G3-A1)  
Here is an simplified Example table with 3 columns.

VEHICLE ID	ECU	DTC Code
3	G1	A1
2	G1	A1
5	G3	A1
3	G1	A2
3	G3	A2
4	G4	A2
1	G1	A2
2	G2	A2
3	G3	A1
5	G2	A2
1	G2	A3

Sometimes I need to search for vehicles ID's with two Error Codes using an AND condition.

Example: Which Vehicle IDs have both Error Codes "G2-A2" AND "G1-A1". 

If I manually look in the table I can see Vehicle ID 2 is the only Vehicle with both Error Codes. 

But see no solution to implement this in my Power BI report with my beginner knowledge.

If the report was working as I want to, the result would look something like this:

VEHICLE ID	ECU1	DTC Code1	ECU2	DTC2 Code 2
2	G1	A1	G2	A2

I was thinking maybe need to duplicate the existing table, so that everything is in two separate tables.

ECU1 / DTC Code 1 from the first (orignal Table) and ECU2 / DTC Code 2  from the duplicate table.

I tried this but still had no luck.  Maybe there is better ways. 

Is it possible to set up my Power BI report so I can use multible filtering on same table?

[JoaoMarcelino · Accepted solution]

Hi @Anonymous !

From what I can understand, what you need is to merge the table with itself and then expand only the columns you want. After that, you can create a new column that is the concatenation of the 4 columns. In the end, as safety, you should remove the duplicates of the whole table. Lets start? 🙂

1. Open Power Query

2. Click on the query you want and choose Merge

3. Choose itself and merge it by vehicleid

4. Click on Expand and choose the columns you want to bring

5. Add new column and write something like this:
= [ECU]&"-"&[DTC Code]&"&"&[ECU.1]&"-"&[DTC Code.1]
(keep in mind you need to adjust the name of the columns in accordance)

6. Remove duplicates on the new column "Both codes" to prevent mistakes and data duplication

This should do the trick! From now on you can use the new column as filter to your reports and analysis 🙂

Sample file attached if needed

Hope I was of assistance!
Cheers
Joao Marcelino

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!