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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Vvelarde
Community Champion
Community Champion

@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

8 REPLIES 8
MarcelBeug
Community Champion
Community Champion

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)
Vvelarde
Community Champion
Community Champion

@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

Thanks Vvelarde!

That works to filter results when selecting a territory, is there a way to also allow a SELECT ALL? So in this example, you would be able to use the slicer to Select All territories or to allow for multi-select?

Hello there, I have same question too. Let say the table is:

Territory 1Territory 2Territory 3
0.230.450.65
0.320.50.7
0.40.560.75

Then, I want to put in one slicer, show list of :
Slicer :
Territory 1
Territory 2
Territory 3

Then, when I click Territory 1, the data in territory will display on line chart. Then, If I click Territory 2, then the line chart will display territory 2 data only. 

Hope you can help me. thank you in advance.

 

What is "T" in the measure?

 

Thanks

Kg

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

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)
Anonymous
Not applicable

That worked! Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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