Use a slicer to filter an unrelated table using a mock CONTAINSX calculation

[BSLATTER]

Hello,

I am attempting to allow users to select a value (Fiscal Period) and that will drive many other visual calculations. They will select this Period in a slicer.

In some calculations the period will be a max value, whereas others it will be the only value. So for that reason I cannot have them pick all the periods they want to look at. IE:
If they pick Period 2, it would show Sales in Period2, but show Inventory from Period 1 and 2 (Where Period < MAX(SelectedPeriod))

The issue is they COULD pick multiple periods. IE:
User picks Period 2 and Period 4, it would shows sales in P2 and P4, but show Inventory from P1-P4 (< MAX selected)

To accomplish this, I have a created table with no joins to any other table in the data and just has the periods that we are allowing users to select from. This works except for the situations where it would show only the specific Periods they select. I cannot get the calculations to compare a single value against a table of multiple values.

I did find this article CONTAINSX which gave me this calculation:

PeriodExists = IF(

COUNTROWS(

FILTER('FiscalPeriodDateAggDim (2)',

SEARCH('FiscalPeriodDateAggDim (2)'[PeriodFilterKey],

FiscalPeriodDateAggDim[PeriodFilterKey], 1, 0)

)

),

1,

0

)

This does 95% of what I want of providing the join between the two tables (Both FiscalPeriodDateAgg and FiscalPeriodDateAgg2) - except it's ignoring when I filter on the value [PeriodNum] in the slicer, and not filtering those out of FiscalPeriodDateAgg2 in order to effect the first table.

Sample data:

FiscalDateAggPeriod

 

Year	Period	PeriodOffset	PeriodName	PeriodFilterKey	Period Exists
2018	10	-5	Period 10	10BLANKPeriod 10BLANK	0
2018	11	-4	Period 11	11BLANKPeriod 11BLANK	0
2018	12	-3	Period 12	12BLANKPeriod 12BLANK	0
2019	1	-2	Period 1	1BLANKPeriod 1BLANK	1
2019	2	-1	Period 2	2BLANKPeriod 2BLANK	1
2019	3	0	Period 3	1BLANKPeriod 1BLANK	0

FiscalDateAggPeriod2

Year	Period	PeriodOffset	PeriodName	PeriodFilterKey
2019	1	-2	Period 1	1BLANKPeriod 1BLANK
2019	2	-1	Period 2	2BLANKPeriod 2BLANK

Goal: Have a slicer on "PeriodNumber" in FiscalDateAggPeriod2 and have the above calculation (PeriodExists) be affected by that filter. IE: Select Period 1 only and have Period 2 value in PeriodExists change to 0

[v-danhe-msft · Accepted solution]

Hi @BSLATTER ,

Based on my test, you could refer yo below formula:

Measure = IF(CALCULATE(SUM(FiscalDateAggPeriod[Period]))<=CALCULATE(MAX('FiscalDateAggPeriod2'[Period]),FILTER('FiscalDateAggPeriod2','FiscalDateAggPeriod2'[PeriodName]=SELECTEDVALUE(FiscalDateAggPeriod2[PeriodName]))),1,0)

Result:

Regards,

Daniel He