Filter table based on combination of values from Slicer

[kev_oactive]

Hi everyone, 

I have a table with clients and categories, where each client can have multiple Categories tied to it. It looks something like this: 

Client	Category
1	A
1	B
1	C
2	A
2	B
3	A
4	C
5	A
5	B
5	C
6	A
7	C
8	A
8	B

I needed to cluster those clients in a way that, in each cluster, all the clients have the same exact categories tied to them. I don't know if this is the best way (and I'm open to suggestions as well), but I created a table like this in order to achieve those clusters: 

Client	Category
1	A,B,C
5	A,B,C
2	A,B
8	A,B
3	A
6	A
4	C
7	C

So, with that last table, I can have the count of clients for each cluser: 

Cluster	Count
A,B,C	2
A,B	2
A	2
C	2

The requirement now is, they want a Category slicer to filter the Clusters, but the expected behavior is the following: 

• If I select a single value of that slicer, for example, Category A, they want to filter just the 'A' Cluster. In this case, I'll have clients 3 and 6
• If I select two values, for example, Categories A and B in the dropdown, they want to filter the Clusters 'A', 'B', and 'A,B'. In this case, we will have clients 3, 6 (A), 2 and 8 (from cluster 'A,B')
• Same goes for multiple values, for example, I select Categories A, B and C in the dropdown, filtering Clusters 'A', 'B', 'C', 'A,B', 'B,C', 'A,C', 'A,B,C' (which in this case, it will bring every client)

Is there anyway to do this??? 

Thank you so much for your time and help. 

Regards.  

[Icey · Accepted solution]

Hi @kev_oactive ,

---

I needed to cluster those clients in a way that, in each cluster, all the clients have the same exact categories tied to them. I don't know if this is the best way (and I'm open to suggestions as well), but I created a table like this in order to achieve those clusters: 

Client	Category
1	A,B,C
5	A,B,C
2	A,B
8	A,B
3	A
6	A
4	C
7	C

---

For this requirement, it is suggested to create a calculated measure or calculated column like below:

Cluster Measure = CONCATENATEX('Table',[Category],", ")

Cluster Column =
CONCATENATEX (
    FILTER ( 'Table', 'Table'[Client] = EARLIER ( 'Table'[Client] ) ),
    [Category],
    ", "
)

---

So, with that last table, I can have the count of clients for each cluser: 

Cluster	Count
A,B,C	2
A,B	2
A	2
C	2

---

Then, considering the need to calculate the count of clients for each cluser, we use the calculated column - "Cluster Column".

---

The requirement now is, they want a Category slicer to filter the Clusters, but the expected behavior is the following: 

• If I select a single value of that slicer, for example, Category A, they want to filter just the 'A' Cluster. In this case, I'll have clients 3 and 6
• If I select two values, for example, Categories A and B in the dropdown, they want to filter the Clusters 'A', 'B', and 'A,B'. In this case, we will have clients 3, 6 (A), 2 and 8 (from cluster 'A,B')
• Same goes for multiple values, for example, I select Categories A, B and C in the dropdown, filtering Clusters 'A', 'B', 'C', 'A,B', 'B,C', 'A,C', 'A,B,C' (which in this case, it will bring every client)

---

For this requirement, it is suggested to create a measure and put it on the field of "Filters on this visual".

Filter Measure = 
VAR SelectedCate_ =
    VALUES ( 'Table'[Category] )
VAR SelectedCate_1 =
    ADDCOLUMNS (
        SelectedCate_,
        "Cate_", CONCATENATEX ( SelectedCate_, [Category], ", " )
    )
VAR SelectedCate_2 =
    DISTINCT ( SUMMARIZE ( SelectedCate_1, [Cate_] ) )
VAR SelectedClusterColumn_ =
    VALUES ( 'Table'[Cluster Column] )
RETURN
    IF ( INTERSECT ( SelectedClusterColumn_, SelectedCate_2 ) <> BLANK (), 1 )

And I create an auxiliary measure to help you understand better. Then see the test result:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kev_oactive]

Thanks a lot man!! 
I'm fixing a couple of issues I have because my cateogries are strings and the filter is not quite there yet, but this solution helped me a lot. 

[parry2k]

@kev_oactive Wow, this is an interesting and exciting problem to solve. I think I know the solution but before I get into it I want to know, in the real database how many distinct categories you have? It will help to derive the solution. 

[kev_oactive]

Hey, thanks for answering. So far we have around 20 categories. But in can vary depending on different filters.