Forum Discussion

Shanej100's avatar
Shanej100
Regular Visitor
3 years ago
Solved

Filter Table in PowerBi based on a slicer

Hello,

 

I've got a filtering question, that despite searching for days I can't seem to solve.

 

To keep it simple:

 

I have a table that contains Business name, business group and transaction volume.  Very simple. 

 

1 business can be part of only 1 group, but 1 group can contain many businesses.

 

I have a slicer on my dashboard where one can select the Business name, which filters a data card to show transaction volume for that specific business.  

 

What I want is a table that will display all business that are part of the group that the business in the slicer is part of. 

 

I appreciate any help on this.

 

Cheers

Shane

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Shanej100 

    According to your description, you want to put the [Business name] in the slicer and show all businesses that currently exist for the group.

    For your needs, it's custom slicer filtering, so we need to create a new slicer table.

    We can click "New Table", and enter this to create a table as  a slicer:

    Business name Slicer = VALUES('Table'[Business name])

    Then we need to click "New measure" and enter this:

    Measure = var _name= VALUES('Business name Slicer'[Business name])
var _groups =SELECTCOLUMNS( FILTER(ALLSELECTED('Table') , 'Table'[Business name] in _name) ,"group" ,[Business group])
var _cur_group = MAX('Table'[Business group])
return
IF(_cur_group in _groups ,1,0 )

     

    In the end , we can put the 'Business name Slicer' on the slicer visual and configure the measure on the "Filter on this visual" , like this:

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Shanej100 

    According to your description, you want to put the [Business name] in the slicer and show all businesses that currently exist for the group.

    For your needs, it's custom slicer filtering, so we need to create a new slicer table.

    We can click "New Table", and enter this to create a table as  a slicer:

    Business name Slicer = VALUES('Table'[Business name])

    Then we need to click "New measure" and enter this:

    Measure = var _name= VALUES('Business name Slicer'[Business name])
var _groups =SELECTCOLUMNS( FILTER(ALLSELECTED('Table') , 'Table'[Business name] in _name) ,"group" ,[Business group])
var _cur_group = MAX('Table'[Business group])
return
IF(_cur_group in _groups ,1,0 )

     

    In the end , we can put the 'Business name Slicer' on the slicer visual and configure the measure on the "Filter on this visual" , like this:

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

    • Shanej100's avatar
      Shanej100
      Regular Visitor
      3 years ago

      Amazing!  Thank you so much.  This worked perfectly!