Design a slicer that selects a value from ColumnA but filtering the all values from columnB

[JasonXieee]

Dear PBI friends,

I have a slicer and a bar chart controlled by the slicer. I want to achieve a function that I select a value X1 in the slicer (using column A), then it filters all the values in Column B whose column A is X1. In other words, I am selecting one column but actually want to filter based on the value in another column.

I draw a table for understanding. My data would be like below. When I select a value in Column Supplier in the slicer (like select Supplier A), the controlled table will return rows in blue (because these are supplier A's products). 

Actually, I want to do a analysis to know how many Supplier-A-products are sold by other suppliers. I have a bar chart showing all the suppliers. I also want to compare the prices of these product, so that fliter function is the beginning of everything.

I feel like this can be achieve easlier but I am stuck. Off the top of my mind, I think the cross table many-to-many relationship can achieve that, but that is too risky. Do you have any good idea?

Many thanks!

Jason 

[DataInsights · Accepted solution]

@JasonXieee,

Try this solution.

1. Create a clone of the main table using Power Query or DAX. This example uses a DAX calculated table:

SlicerTable = SUMMARIZE ( MainTable, MainTable[Supplier], MainTable[Product] )

2. Create a slicer using SlicerTable[Supplier]. The table visual uses MainTable fields.

3. Create measure:

Visual Filter = 
VAR vProducts =
    VALUES ( SlicerTable[Product] )
VAR vResult =
    SUMX ( FILTER ( MainTable, MainTable[Product] IN vProducts ), 1 )
RETURN
    vResult

4. Add the measure [Visual Filter] as a filter to the table visual:

[DataInsights · Accepted solution]

@JasonXieee,

Try this measure:

Product Count = 
CALCULATE (
    COUNT ( MainTable[Product] ),
    TREATAS ( VALUES ( SlicerTable[Product] ), MainTable[Product] )
)

Use MainTable[Supplier] in the visual. The meausure [Visual Filter] isn't needed for the bar chart.

[DataInsights · Accepted solution]

@JasonXieee,

Try this solution.

1. Create a clone of the main table using Power Query or DAX. This example uses a DAX calculated table:

SlicerTable = SUMMARIZE ( MainTable, MainTable[Supplier], MainTable[Product] )

2. Create a slicer using SlicerTable[Supplier]. The table visual uses MainTable fields.

3. Create measure:

Visual Filter = 
VAR vProducts =
    VALUES ( SlicerTable[Product] )
VAR vResult =
    SUMX ( FILTER ( MainTable, MainTable[Product] IN vProducts ), 1 )
RETURN
    vResult

4. Add the measure [Visual Filter] as a filter to the table visual:

[JasonXieee]

@DataInsights  Hello, thanks for the prompt and detailed answer!

I got the gist of your solution and followed through. However, it still does not work in the end—the slicer and the table are still not interacting. Maybe I made some mistakes in the middle. May I ask:

1. In step 3, when creating the measure, which table do we create in? I tried both (the clone and the main one), but they both did not work in the end.

2. I have put the measure, [Visual Filter], to the Filter Pane of my table visual. When I did a selection in my slicer, the [Visual Filter] in the Filter Pane of my table visual did not change (it is still "All"). I checked other posts saying that a slicer will not change the content in the filter pane. I wonder if our [Visual Filter] measure will change accordingly.

I screenshot it below. MPN means product code. I have selected Gilson in my slicer; however, no matter what I select in my slicer, the table below won't change.

Hope to get more guidance from you! Thank you so much.

Jason

[DataInsights]

@JasonXieee,

Answers to your questions below:

1. It doesn't matter which table you create the measure in. The logic of the measure is independent of the measure's home table. It's best practice to create a measure table that contains all your measures.

2. You need to specify "greater than 0" for the filter. See step 4 in my solution.

No relationship should exist between the two tables. The point of this solution is to create a disconnected table that allows you to filter via DAX instead of a relationship.

[JasonXieee]

Dear @DataInsights ,

Oh yeah! The "greater than 0" does the magic. I overlooked this setting. I would use your solution instead of the table relationship since it feels more stable.

But sorry may I have a final question: In Table/Matrix Visual, the data is filtered correctly, but when I turn it into a bar chart (supplier as the X-axis while count (product) as the Y-axis), each bar shows the number of all products in each supplier, instead of the number of overlapped (filtered) products. The selection in the slicer won't change the number in each bar. Do you know why that is? Or what measure should I put into the Y-axis? 

Many thanks,

Jason

[DataInsights · Accepted solution]

@JasonXieee,

Try this measure:

Product Count = 
CALCULATE (
    COUNT ( MainTable[Product] ),
    TREATAS ( VALUES ( SlicerTable[Product] ), MainTable[Product] )
)

Use MainTable[Supplier] in the visual. The meausure [Visual Filter] isn't needed for the bar chart.

[JasonXieee · Accepted solution]

Hello @DataInsights 

That works perfectly.

I haven't tried the TREATAS function before. It is quite useful for controlling between two tables. Thank you for sharing that. I am really grateful for your time and effort on this. Wish you a nice day.

[DataInsights]

@JasonXieee,

Glad to hear that works. Yes, TREATAS is quite useful. Happy to help!

[JasonXieee]

@DataInsights  I found something that makes the table moving. It seems we also need to build a connection between two tables (the clone and the main one). I am testing the right way and the right column to connect them! Thank you!

But now the tricky part is...even if I remove the [Visual Filter] in the Filter Pane of my table visual. It will keep the interaction. So it seems it is just the table relationship making effect, which is what I guess from my question... Nevertheless, I felt unstable in control based on the table relationship.

So how do we use a measure-filter to interact?