Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
kev_oactive
Regular Visitor

Filter table based on combination of values from Slicer

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: 

ClientCategory
1A
1B
1C
2A
2B
3A
4C
5A
5B
5C
6A
7C
8A
8B

 

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: 

 

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

 

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

ClusterCount
A,B,C2
A,B2
A2
C2

 

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.  

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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],
    ", "
)

 

client1.PNG

 


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".

client2.png

 


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 )

 

client3.PNG

 

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

Filter table based on combination of values from Slicer.gif

 

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

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],
    ", "
)

 

client1.PNG

 


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".

client2.png

 


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 )

 

client3.PNG

 

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

Filter table based on combination of values from Slicer.gif

 

 

Best Regards,

Icey

 

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

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
Super User
Super User

@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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.