cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Want to combine multiple columns into one for use in a Slicer

This might be a bit hard to explain, so I'll correct as needed.

 

I have a table with three columns all containing Territory information, but representing three different territory groupings, as follows:

 

Territory 1Territory 2Territory 3
NYNYCA
NJPAWA
PADEPA
 ME 

 

In my report, I would like to create a single slicer that works across all three territories. So if I am looking at Sales data for PA, I would like to select PA from a singler slicer and have it apply to any visualization that contains PA in any of the three territory groupings. I have tried extracting these into a separate table and then joining on that table three times. Did not work. I tried creating three versions of that table and joining on each, but again this did not work. 

 

I was thinking I could create a single column that contains potentially contains all three values, and then only displays the one value that is represented in the slicer (e.g. return PA if it exists in any of the three columns), and then I could join on that single column. But I can't figure out how to do this in DAX - even though it would not be hard to do in Excel formulas using concatenate and find). 

 

Any thoughts on if/how this can be done?

 

Best,

Scott

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Want to combine multiple columns into one for use in a Slicer

@Anonymous

 

Let me help you with your question:

 

1: Create a New Table with this Dax:

 

Territories =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 1] ),
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 2] ),
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 3] )
    )
)

Use this table as slicer.

 

2.  Create a measure:

 

ShowRow =
IF (
    HASONEVALUE ( Territories[T] ),
    IF (
        COUNTROWS ( FILTER ( Table1, Table1[Territory 1] = VALUES ( Territories[T] ) ) )
            || COUNTROWS ( FILTER ( Table1, Table1[Territory 2] = VALUES ( Territories[T] ) ) )
            || COUNTROWS ( FILTER ( Table1, Table1[Territory 3] = VALUES ( Territories[T] ) ) ),
        1,
        0
    )
)

Use this measure as Visual Filter in a table with your other 3 columns.---to show only when is 1.

 

I hope this help you.

 

 

 

 




Lima - Peru

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: Want to combine multiple columns into one for use in a Slicer

@Anonymous

 

Let me help you with your question:

 

1: Create a New Table with this Dax:

 

Territories =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 1] ),
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 2] ),
        SELECTCOLUMNS ( Table1, "T", Table1[Territory 3] )
    )
)

Use this table as slicer.

 

2.  Create a measure:

 

ShowRow =
IF (
    HASONEVALUE ( Territories[T] ),
    IF (
        COUNTROWS ( FILTER ( Table1, Table1[Territory 1] = VALUES ( Territories[T] ) ) )
            || COUNTROWS ( FILTER ( Table1, Table1[Territory 2] = VALUES ( Territories[T] ) ) )
            || COUNTROWS ( FILTER ( Table1, Table1[Territory 3] = VALUES ( Territories[T] ) ) ),
        1,
        0
    )
)

Use this measure as Visual Filter in a table with your other 3 columns.---to show only when is 1.

 

I hope this help you.

 

 

 

 




Lima - Peru

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Want to combine multiple columns into one for use in a Slicer

Example in Power BI Desktop:

Table TeSt (Territory State) with fields Territory and State. Rows for each combination (so 10 rows).

Table State with field State and 1 row for each state.

Table SalesByState with fields State and Sale

 

Create relationships:

State - TeSt on field State

State - SalesByState on field State

 

Make sure the relationship State-TeSt has "Cross filter direction" set to "both" in Manage Relationships - Edit Relationship.

Now you can create a slicer on territory and it will work as intended.

 

Specializing in Power Query Formula Language (M)
Highlighted
Anonymous
Not applicable

Re: Want to combine multiple columns into one for use in a Slicer

That worked! Thanks!

Highlighted
New Member

Re: Want to combine multiple columns into one for use in a Slicer

Hello guys,

 i would like to filter a Waterfall report with a slicer like the following:

 Slicer                                WaterFall Report

2010                                 2009 and 2010

When selected a Year value in the slicer show in the report the corresponding Year records and  Year-1 records

 

Any Idea to reach this?

 Thanks in advance

Highlighted
Community Champion
Community Champion

Re: Want to combine multiple columns into one for use in a Slicer

This has little to do with the original topic.

 

Please create your own topic, so it will stand out as not solved yet and having zero replies.

Specializing in Power Query Formula Language (M)
Highlighted
Helper III
Helper III

Re: Want to combine multiple columns into one for use in a Slicer

What is "T" in the measure?

 

Thanks

Kg

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors