A slicer for multiple columns

[Anonymous]

I am working on a project and I would like a slicer to search all columns within the table. Below is an example:

Names	Vote 1	Vote 2	Vote 3	Vote 4	Vote 5	Vote 6	Vote 7	Vote 8	Vote 9	Vote 10
Jack	Connor	Sian	Paul	Luke	Sian	Jimmy	Connor	Jimmy	Paul	Harry
Harry	Jack	Luke	Jack	Connor	James	Connor	Luke	James	Harry	Luke
Gary	Jimmy	Becky	James	James	James	Jimmy	Harry	James	James	Sean
Luke	Harry	James	Gary	Richard	Luke	James	Gary	Sean	Sean	Luke
James	Becky	Paul	Harry	Harry	James	Connor	James	Connor	James	Becky
Richard	Harry	Jack	Sean	Richard	Sean	James	Connor	Paul	Luke	Luke
Connor	Harry	Becky	Becky	Sian	Richard	Harry	James	Jimmy	Gary	Luke
Sian	Luke	Sean	Harry	Jack	Becky	Jack	Gary	Gary	James	Sian
Luke	Jack	Sean	Richard	Connor	Luke	Luke	Harry	Becky	Jack	Luke
Hannah	Paul	Paul	Harry	Richard	Sian	Sian	Harry	Sian	Paul	Becky
Jimmy	Luke	Sian	James	Sean	Becky	Luke	Luke	Luke	Harry	Luke
Sean	Jack	Sian	Luke	Richard	Richard	Jimmy	Connor	Sian	Jack	Harry
Paul	James	Richard	Gary	Jack	Harry	Gary	Leah	Harry	Jack	Gary
Becky	James	Harry	Richard	Leah	Jimmy	Connor	Luke	Connor	Richard	Harry
Leah	Richard	Harry	Connor	Connor	Jack	Harry	Harry	Harry	Jack	Gary

I would like to use the column "Names" for the slicer. Therefore, when I clicked a name it would display all the people that voted for them in a table. For example, if I clicked the name Jack it would display the following:

Names	Vote 1	Vote 2	Vote 3	Vote 4	Vote 5	Vote 6	Vote 9
Harry	Jack	Luke	Jack	Connor	James	Connor	Harry
Richard	Harry	Jack	Sean	Richard	Sean	James	Luke
Sian	Luke	Sean	Harry	Jack	Becky	Jack	James
Luke	Jack	Sean	Richard	Connor	Luke	Luke	Jack
Sean	Jack	Sian	Luke	Richard	Richard	Jimmy	Jack
Paul	James	Richard	Gary	Jack	Harry	Gary	Jack
Leah	Richard	Harry	Connor	Connor	Jack	Harry	Jack

It can be seen that Harry, Richard, Sian, Luke, Sean, Paul and Leah all voted for Jack within their 10 votes.

Currenly if I click Jack it just shows me what he has voted for his 10 votes.

Is the possible? Any help is much appreciated.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Names(a calculated table):

Names = DISTINCT('Table'[Names])

You may create a measure as below.

Visual control = 
var tab = {MAX('Table'[Vote 1]),MAX('Table'[Vote 2]),MAX('Table'[Vote 3]),MAX('Table'[Vote 4]),MAX('Table'[Vote 5]),MAX('Table'[Vote 6]),MAX('Table'[Vote 7]),MAX('Table'[Vote 8]),MAX('Table'[Vote 9]),MAX('Table'[Vote 10])}
var result = 
COUNTROWS(
    FILTER(
        DISTINCT(Names[Names]),
        COUNTROWS(
            FILTER(
                tab,
                [Value]=[Names]
            )
        )>0
    )
)+0
return
IF(
    result>0,
    1,0
)

Then you need to put the measure in the visual level filter to filter the result.

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

you need another table

SELECT vote1 as filterName, name

Union

SELECT vote2, name

Union

SELECT vote3, name

......

Etc

Then make a relation between the new table and the current based on name

Make a filter on the field FilterName

[Anonymous]

Thank you for the reply but SELECT doesn't seem to be an expression when I am creating another table. The code doesn't seem to work.