Conditional filtering on unrelated table

[robmarsh]

I am looking for a way to conditionally summarise and filter a table based on slicer selections in another, unrelated table.

My Sales fact table that stores transactions looks like the simplified data set below:

Date	Category	Sub-Category	Value
01/01/2024	Sports	Football	1000
01/01/2024	Sports	Football	2000
02/01/2024	Sports	Rugby	500
03/01/2024	Games	Backgammon	350
03/01/2024	Sports	Tennis	600
03/01/2024	Games	Backgammon	1250
04/01/2024	Games	Chess	750
04/01/2024	Games	Backgammon	500
05/01/2024	Sports	Football	300

I then have two slicers setup on the Category and Sub-Category columns in the Sales fact table.

I also have an unrelated table called Targets that look like below:

Category	Sub-Category	Target Value
Sports	Football	1000
Sports	Rugby	1500
Sports	Tennis	750
Games	Backgammon	1250
Games	Chess	500

What I need is a measure that conditionalliy summarises the Target Value on the Targets table based on the slicer selections applied to the Sales table. So, for example, if no selections were made on the slicers, the measure would be return the sum of all the row values in the Targets table = 5000. If the user selected "Sports" in the Category slicer, the measure would return a total value = 3250. If the user removed the slicer on the Catgory, but selected "Backgammon" on the Sub-Category slicer then the measure would return a value = 1250.

I got as far as creating the measure that used variables to store the selected values on the slicers, and then calculate the vales based on those selections like this:

measure = 

var SelectedCategory = SELECTEDVALUE('Sales'[Category])
var SelectedSubCategory = SELECTEDVALUE('Sales'[Sub-Category])

var TargetValue = CALCULATE(
    SUM('Targets'[Target Value]),
    FILTER(
        'Targets',
        'Targets'[Catgeory] = SelectedCategory &&
        'Targets'[Sub-Category] = SelectedSubCategory 
    )
)

RETURN TargetValue 

This works, but obviously only if there are selected values in both slicers. If possible, I want to try and avoid doing multiple nested IF expressions based on whether r not each slicer has a selected value, my actual scenario is more complex with more slicers, so would require more permutations and combinations to nest! My background is database programming, so I instantly thought of writing dynamic DAX statements, but after a bit of searching it appears there isn't an equivalent in DAX. I am also thinking I might be missing a really simple way to do this, but my head is stuck in overthinking mode, so any help greatly appreciated!

[robmarsh]

Thanks both for your replies. After doing some testing, both suggestions work, although the ALLSELECTED() function in place of SELECTEDVALUE() in combination with using IN() with the CALCULATE() function is closer to what I was hoping to achieve.

There was also another way too which was to create two dimension tables with one column for Category and Sub-Category. Then in the model, create relationships between the Sales fact table and also the Targets table too. Then change the slicers to run from these Category and Sub-Category dimension tables. and the filters propagate through to both the Sales and Targets table. This also has the advantage of my slicer options not jumping around depending on previous slicer selections (I think from reading previous posts, slicers on dimension tables is better practice than slicers on fact tables, partly for this reason).

Thanks for the help though 🙂

[talespin]

hi @robmarsh 

Create this measure:

Summ =

VAR _SelectedCat = ALLSELECTED('Fact'[Category])

VAR _SelectedSubCat = ALLSELECTED('Fact'[SubCategory])

RETURN CALCULATE(SUM(Summary[Target Value]), Summary[Category] IN _SelectedCat && Summary[SubCategory] IN _SelectedSubCat)

[lbendlin]

read about TREATAS and VALUES.