Yet another Slicer Question: Using a chart as the slicer for a table

[10500438]

I have a clustered column chart which has the X axis as product group and the legend as market type. 

I have 2 product groups, HR, HRPO ,each product has 3 market types (Auto, Pipe, Trade). 

I also have a table, the table has Product Group and Market type, below is an example (but imagine HRPO listed under the next segment of the table)

When the user selects HR Trade in the column chart i want the table to filter to Product Group HR but not specifically to Trade, I want all three market types available for review.  I am getting the results as shown below which is not desired.

What i would like to see it this

I originally tried to pass the selection value (in this case "HR") from the clustered column chart to the slicer (the slicer would be Product Group) and turn all visuals off from the slicer except the table, but I did not know how to successfully write this in DAX.

I cannot use a slicer on the page because the user has to determine what is displayed in the table (in reality we have way more product groups that 2). Any advise is appreciated.

[jdbuchanan71]

@10500438 

If you select the category label in the chart rather than one of the data elements you should get the filtering you want.

[parry2k]

@10500438 one way is what @jdbuchanan71 mentioned but in this user have to click on category on x-axis.

If you really want use to click on any of the bar, it show all market types of the category regardless where a user click, you need to follow these steps and it would achieve but I think it is over kill but want to share. I recommend to use @jdbuchanan71's solution.

First you have to have two dimensions in your model, one for category and one for market type, set relationship between your data table with both of these dimensions. 

use category and market type in your cluster chart from these dimensions and value from data table. So you will get your visual similar to what you are seeing right now.

Here is what you need for table visual:

In power query, right click on your data table and select reference from popup menu. It will create another table, name it whatever you want. close and apply the changes.

Now with this new table you create, set a relationship of this table with category but not with market type table. In table visual, add category from dimension table and market type from new table and value from new table.

Once this is done, now when you click on bar or axis in your bar chart visual, table visual will always show all market place for each category.

As I said before it is over kill, but if it really what your user want, it can be achieved. Let me know if something is not clear and I can send you pbix file. Cheers!!

[10500438]

Thank you Jdbuchanan71 but my users will not know to click the axis (its a large user group), they are accustom to clicking the chart and filtering the data accordingly.

parry2k, I have created something similar before, so I understand the concept of using a table for dynamic selection but can you please show me because I am not understanding the layout by reading (I am a very visual learner) or is this what you mean?

[jdbuchanan71]

@10500438 

I would say that if they click the Trade bar in the HR category the data has been filtered according to their selection.  They selected both a market and department.  If you can, change their behavior now so they learn they can select the category axis in a chart.  If you don't you will be creating the bridge tables a lot.  

Don't fight the tool.

[parry2k]

@10500438 totally agee wiht @jdbuchanan71  but still sending you the solution.

[parry2k]

@10500438 in my data model

year = product group

category = market type

[10500438]

I attempted your solution but I believe my issue is that my data set is connected to another data set with a unique key as the relationship join. These data sets are rather large, as well and all intertwined, very frustrating.

Is there some DAX code that will pass a chart selection to a Slicer? I could turn all charts off to the slicer except the table, this way my table changes to the product group (without removing the market types,) but the other charts are specific to the market type selected in the chart.

[parry2k]

@10500438 I think the best is if you can provide pbix wiht sample data

(remove any sensitive info before sharing) and I can look into it.