Filters with multiple value

[sshanssun]

Hi, I have a value column and a category column. The values can be multiple in single row and for each value, there's a category to be named.

For example, below is how the category been named for each value,

Value	Category
1	Type A
6	Type B
300	Type C
MAM1001	Type D
MRA2002	Type E

Here is how the actual master table will look like for the value. I would need the category to be displayed accordingly,

Value	Category (expected results)
1,6	Type A, Type B
1	Type A
MAM1001	Type D
6,MRA2002	Type B, Type E
MRA2002	Type E

My result is expected to under Category column. I would appreciate if you could help get me the category column in Power BI visual matrix table. There should be a filter for Category and if I do filter Type A, the single and multiple combination of value should be displayed in the matrix table. I appreciate your help on this. Thank you!

[v-sathmakuri]

Hi @sshanssun ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[sshanssun]

This hasn't been resolved. But I asked a different question in a different thread which had helped me to resolve my query and I accepted as resolved on that thread, here is the link of that, Solved: Re: CONCATENATEX gives different result - Microsoft Fabric Community

[rohit1991]

Hi @sshanssun,
To achieve your expected result in Power BI where each row in a matrix visual correctly displays the corresponding categories based on a comma-separated list of values, you'll need to use Power Query to transform the data. Start by splitting the Value column (which contains one or more values separated by commas) into individual rows using the "Split Column by Delimiter" option and choosing to split into rows. This normalizes the data so that each row contains a single value. Next, trim any extra spaces from the split values to ensure they match correctly, and then perform a merge with your category mapping table based on the Value field. 

After the merge, expand the category column so each value now has its corresponding category. Then, group the data back together using a unique identifier (such as an index or original row value), and use the Text.Combine function to concatenate the matched categories into a single, comma-separated string. This new transformed table will allow you to use the Category column in a matrix visual and filter it effectively—so if you filter by “Type A”, any rows where Type A appears (even in combination with others) will still be shown.

[sshanssun]

Hi Rohit, I tried applying the steps you had sent, thank you for that. However, the data I am having is around 3Million line item and after applied the merge query, the table from power query chocked when it tried to refresh the data to view. I have attempted nearly 5 times, finally the report got crashed. Is there any alternative or a similar one which @V-AHY suggested but I do have question on that and waiting for a solution to that. Thanks again!

[rohit1991]

Hi @sshanssun ,

3 million rows definitely adds a performance challenge when working with transformations like split and merge in Power Query, especially if it’s all being done in-memory. If the merge step is causing Power BI to crash, an alternative approach might be to shift part of the logic into DAX or optimize the query process by pre-processing the mapping logic outside Power BI, if possible

However, if you’d still prefer to do it within Power BI, one workaround would be to load the mapping table separately and use a DAX-based calculated column or measure to retrieve the matching categories for each individual value dynamically. While this won’t offer the same flexibility as the Power Query approach in terms of splitting and merging, it can significantly reduce processing time and allow filtering by category more safely.

Another approach is to preprocess your data in Power Query but in stages, avoiding doing everything at once. For example, break the logic into intermediate reference queries, apply filtering early in the process, and avoid expanding or combining large columns until necessary.

Lastly, depending on your environment, it might be worth considering moving the transformation logic into a SQL view or Power Automate flow to offload the heavy lifting before bringing it into Power BI. That way, your model stays lighter, and refresh times remain stable.

[v-aatheeque]

Hi @sshanssun ,
Thanks for reaching out to Microsoft Fabric Cmmunity Forum.

In the master table Add a new column and paste below dax formula to get the expected output.

Category_Result =

CONCATENATEX(

    FILTER(

        'Categories',

        SEARCH('Categories'[Value], 'MasterTable1'[Value], 1, 0) > 0

    ),

    'Categories'[Category],

    ", "

)

Also Attached pbix file for reference.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[sshanssun]

Another example is, please see below result which has highlighted in yellow,

whereas MAM1001 as a separate category of Type D (highlighted in yellow below), the above result shows as Type A, Type D but it should show only Type D for that value.

thank you!

[v-sathmakuri]

Hi @sshanssun ,

Please add a customer column as below from advanced editor to get the expected results.

=Text.Combine(
List.Transform(
Text.Split([Value], ","),
each Record.FieldOrDefault(
Table.SelectRows(Categories, (r) => r[Value] = _){0},
"Category",
"Not Found"
)
),
", "
)

Now the results are showing correctly. Attached the pbix file for reference. 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[v-sathmakuri]

Hi @sshanssun ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri · Accepted solution]

Hi @sshanssun ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[sshanssun]

Thank you for the response. I ran in to another issue. For example, If a "Value" is 1 and its equivalent 'Category' is "Type A", if there's an another "Value" which is 16, the 'Category' shows as "Type A". It appears the "1" value is still been recogonized as "Type A". If there's an 'Value' in MasterTable that is not in the "Categories" table, the "Category_Result" should be resulted as "Other". Please see if this can be done. Thank you once again.

[freginier]

Hey there! 

Make sure that your data is in a normalized format. It looks like you're trying to have the values in a column that can contain multiple categories.

Since Power BI typically handles categories one-to-one (not many-to-many), you can create a measure to concatenate the categories together if there are multiple categories for each value.

Here's a DAX measure to get the expected result (concatenate categories for each value):

CategoryList =
CONCATENATEX(
VALUES('Table'[Category]),
'Table'[Category],
", ",
'Table'[Category],
ASC
)

Then..... 

• Create a Matrix visualization.

• Place the Value column in the Rows section of the matrix.

• Use the CategoryList measure created above in the Values section.

• This will display the concatenated categories for each value.

If you want to filter by specific categories (like Type A), you can add a slicer or filter based on the Category field, and the matrix will show the values corresponding to the selected category.

In the slicer, when you select Type A, it will display values that have Type A in the concatenated category list.

By following this approach, you should be able to get the category column in Power BI to display both individual and multiple categories per value as expected.

Hope this helps!

😁😁

[sshanssun]

Hi, Thanks for the response, I tried applied exactly as you had mentioned however the category list results shows all category for any value, example, if a Value is 1 the corresponding category list shows as Type A, Type B, Type C, Type D, Type E, also any of the row item under List, the Category result shows as Type A, B, C, D, E