Forum Discussion
Filtering with fields that have multiple selections - What are people doing?
My data source that I use for a lot of my reporting allows the end users to select multiple options for various fields.
For example there is a client impacted field. We have a list of important clients such as Apple, Microsoft, X, Uber, Skip.
One Ticket might only impact Apple while the other might impact Apple, X, Uber.
Now when I want to filter this report I get options like:
Apple
Apple, X, Uber
Microsoft
Microsoft , Skip
ETC.
I want the drop down to be clean and just show the list of clients and then be smart enough that if I select Apple it will show me all of the tickets that have Apple as a client whether its by itself or with other clients.
Filter Apple shows:
Apple
Apple, X, Uber.
I have been duplicating my datasets and splitting the column by delimiter to get the cleaned filtering list but this is resulting in duplicate data everywhere since splitting by delimiter duplicates the ticket lines.
I can't do a filter that is like IF Apple exists = True because then I would have a filter for each client and that is terribly ugly.
Any suggestions?
Hi AmberJane ,
It is caused by the filter context, you can create a measure as below to get the count of incidents which fulfill the requirement:
# of Incidents = VAR _selcausedby = SELECTEDVALUE ( 'Caused by'[Caused by] ) RETURN CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', SEARCH ( _selcausedby, 'Table'[Caused by], 1, 0 ) > 0 ) )Best Regards
8 Replies
Hi AmberJane ,
I got what you're trying to do—clean up the filtering.
Here’s a cleaner solution that avoids duplicating data and creating an ugly filter setup:
1. Unpivot the Data to Normalize Client Listings
Instead of splitting by delimiter and creating duplicates, you can unpivot your client data so each client gets its own row for every ticket. This way, Apple, X, Uber will have three separate rows: one for Apple, one for X, and one for Uber.
Steps:
- If your dataset has a column like "Clients" that stores values like Apple,X,Uber, you can unpivot this column. This process creates a new row for each client.
- So, a ticket with multiple clients (Apple,X,Uber) would create three rows in the data:
- One row for Apple
- One row for X
- One row for Uber
This way, you won’t have to deal with long concatenated strings, and filtering by Apple will work correctly because Apple will now appear in a separate row for each impacted ticket.
2. Create a Many-to-One Relationship
After unpivoting, you can create a relationship between the ticket data (which will now have multiple rows per ticket for each client) and a new Client Table that contains the unique list of clients (like Apple, Microsoft, etc.).
For example:
- Tickets Table: Now has one row per ticket/client combination (e.g., Apple, X, Uber).
- Client Table: Contains a list of unique clients (e.g., Apple, Microsoft, X).
This way, when you filter on Apple in your report, Power BI will show all tickets that have Apple—whether Apple is the only client listed or part of a multi-client combination.
3. Using a Slicer with the Clean Client List
Now you can create a slicer using your Client Table. The slicer will show only the clean list of clients (Apple, Microsoft, etc.), and when you select Apple, it will automatically show all tickets where Apple is present, even if there are other clients listed in the same ticket.
4. Advanced Filtering (Optional)
If you want more flexibility, you can add a DAX measure that checks if a specific client (like Apple) appears in any of the tickets and return the corresponding result. But, typically, the unpivot approach should give you a clean and scalable solution.
Please mark this as solution if it helps you, Appreciate Kudos.
How is Unpivot the Data to Normalize Client Listings different from splitting by delimiter? When I split by delimter I get one row for each client ticket which effectively duplicates the data.
Dear AmberJane ,
The key difference between unpivoting the data and splitting by delimiter lies in how the data is structured and how the relationships between the data are managed:
Splitting by Delimiter:
When you split the client field by a delimiter (e.g., a comma), you essentially create multiple rows for a single ticket, each containing one of the clients. But the ticket information is duplicated across these rows, meaning the rest of the ticket data (like ticket ID, issue description, etc.) is repeated for each client. This creates a lot of redundancy in your dataset, which can lead to bloated data and potential performance issues.
For example, if one ticket impacts three clients (Apple, X, Uber), splitting the column by delimiter results in three rows for that single ticket, which includes redundant information in every row (ticket ID, issue description, etc.). Filtering becomes messy because you’re working with redundant rows, and you might run into problems like aggregating data (such as summing the number of tickets) since you have duplicates.
Unpivoting:
When you unpivot the data, you're reshaping it in a more structured way, without duplicating any ticket data unnecessarily. Instead of splitting and repeating ticket information for every client, you're creating a new row only for the client, keeping the ticket-level information intact.
For example, if a ticket impacts three clients (Apple, X, Uber), unpivoting would give you three separate rows:
- One row for Apple
- One row for X
- One row for Uber
Each of these rows would still reference the same ticket, but you wouldn’t have the full redundancy of the ticket information being repeated three times. The unpivoted structure is cleaner and doesn’t lead to excessive duplication, making it much easier to filter and aggregate data later on.
Please mark this post as solution if it helps you. Appreciate Kudos.
