filtering a table in a bar chart by a list of values from a summarized table

[bsheffer]

I have two tables

1.  One is a merchant table with attributes (merchant_table)

2.  One is an status activity table that shows the status for any month for a merchant, but a merchant can have more than one status in a month (Fact MID_Status_History)

merchant_number    Activity_Month     Change details From

1                                8/1/2021              Seasonal

1                                8/1/2021              Open

1                               7/1/2021              Seasonal

2                                7/1/2021              Seasonal

3                                6/1/2021             Open

I only want merchants that have a seasonal status and a non-seasonal status in the same month  so only  merchant_number =1 above would qualify

It seemed to me that summarizing the actvity table to get the merchants that have both conditions in the same month and then summarize it again to get the unique list of merchants would be the best way to get a count.

so I created this measure

_count_mids_seasonal_and_non_seasonal_status =

var _table = summarize('Fact MID_Status_History', 'Fact MID_Status_History'[MERCHANT_NUMBER], 'Fact MID_Status_History'[ACTIVITY_MONTH]

, "seasonal count", calculate(COUNTROWS('Fact MID_Status_History'), filter('Fact MID_Status_History', 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal"))

, "non-seasonal count", calculate(COUNTROWS('Fact MID_Status_History'), filter('Fact MID_Status_History', 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal")))

var _filtered_table = filter(_table, [seasonal count] > 0 && [non-seasonal count] > 0)

var _mids = summarize(_filtered_table, 'Fact MID_Status_History'[MERCHANT_NUMBER])

var _result = countrows(_mids)

return

_result

If I put it into a card or bar chart I get the correct count.  but if I put it in a bar chart and click on the bar, it doesn't filter the merchant_table that is displayed in another visual.

I tried to put distinct count(merchant_table[merchant_number]) in the bar chart values and use this measure as a visual filter but won't let me set a value in the filter.

I want to filter the merchant table if the merchant_number is in the summarized table _mids.

these are the two visuals.  I'm not showing the values in the lower table for privacy issues

[MFelix · Accepted solution]

Hi @bsheffer ,

This is happening because of the filter context, that in the case of the measure you are calculating comes from the several parts you place on your visualization, howver the other table (vizualization) does not return any values because the result of your measure is a single values (countrows) and there is no link to the merchant numbers.

In this case you need to add some context to the values in the data you gave I have place the month on the axis of the bar chart, has you can see the table get's filtered by clicking the bar:

In this case you just need to had to your bar chart some additional context that makes it filter the table visualization.

Another option is to add the measure you created to the visualization and the table will only show the ones that have values on the measure:

This will not have any iteraction with the bar chart if you only have a single bar but has you can see it filter out only the ones that have the calculation, you can then resize the column on the table to 0 so it does not show or condittional formatting has white.