Manipulating slicer values with single column tables

[belvoir99]

My slicer has a number of values (2, 3, 4, 5). (2, 3) is the first pair and (4,5) is the second pair. The slicer can't be broken up into two slicers. Most of the behaviours work fine except the last one.
I want to use the single column table output as filters in the CALCULATE statement as follows:

CALCULATE(
	COUNTROWS(FactTable),
	DimTable1[SomeID] IN FirstPair,
	DimTable2[SomeOtherID] IN SecondPair
)

The behaviour of the slicer is as follows:

Behaviour 1:
2 x
3 x
4 x
5 x
x = ticked. here all are ticked and all are returned - that's fine.

Behaviour 2:
2
3
4
5
none are ticked but all are returned (the weird behaviour of slicers). But this is fine too.

Behaviour 3:
2 x
3
4 x
5 x

here 2 is ticked and returned from the first pair and both 4 and 5 are ticked and returned from the second pair - that's fine too.

Behaviour 4:
2 x
3
4
5
here 2 is ticked and returned from the first pair but nothing is ticked or returned from the second pair but I want both 4 and 5 to be returned as if they had been ticked.

here is my failed attempt as an IF statement can't return a table, but the logic kind of makes sense. First pair is SexValues and second pair is SENValues. I try to replace the empty set with a set with two values (SENTableAll) via the IF statement:

SSUM Test = 
VAR AllTable = VALUES(PupilYearData[PupilSchoolDataID])
VAR SexValues = SELECTCOLUMNS(CALCULATETABLE( VALUES(PupilGroup[PupilGroupID]), PupilGroup[PupilGroupID] IN {2, 3} ), "Result", PupilGroup[PupilGroupID] - 1)
VAR SENTable = SELECTCOLUMNS(CALCULATETABLE( VALUES(PupilGroup[PupilGroupID]), PupilGroup[PupilGroupID] IN {4, 5} ), "Result", PupilGroup[PupilGroupID])
VAR SENTableAll = DATATABLE( "Result", INTEGER, { {4}, {5} } )
VAR SENValues = IF( ISEMPTY(SENTable), SENTableAll, SELECTCOLUMNS(SENTable, "Result", [Result]) )
VAR FinalCount = 
    IF( MIN(PupilGroup[PupilGroupID]) = 1, 
        COUNTROWS(AllTable),
        CALCULATE(
            COUNTROWS(PupilYearData),
            PupilData[SexID] IN SexValues, 
            SENPYD[SENPupilGroup] IN SENValues
        )
    )
RETURN
FinalCount

[wardy912]

Hi @belvoir99 

 Try using FILTER and IN logic, this ensures the filter context is correctly applied even when slicer selections are empty.
DATATABLE should also be used to provide a default table when SENSelected is empty. This avoids the issue of IF returning a table directly inside CALCULATE.

Here's your revised DAX:

SSUM Test =
VAR SexValues =
    SELECTCOLUMNS(
        FILTER(
            VALUES(PupilGroup[PupilGroupID]),
            PupilGroup[PupilGroupID] IN {2, 3}
        ),
        "Result", PupilGroup[PupilGroupID] - 1
    )

VAR SENSelected =
    SELECTCOLUMNS(
        FILTER(
            VALUES(PupilGroup[PupilGroupID]),
            PupilGroup[PupilGroupID] IN {4, 5}
        ),
        "Result", PupilGroup[PupilGroupID]
    )

VAR SENValues =
    IF(
        ISEMPTY(SENSelected),
        DATATABLE("Result", INTEGER, { {4}, {5} }),
        SENSelected
    )

VAR FinalCount =
    IF(
        MIN(PupilGroup[PupilGroupID]) = 1,
        COUNTROWS(VALUES(PupilYearData[PupilSchoolDataID])),
        CALCULATE(
            COUNTROWS(PupilYearData),
            FILTER(
                ALL(PupilData),
                PupilData[SexID] IN SexValues
            ),
            FILTER(
                ALL(SENPYD),
                SENPYD[SENPupilGroup] IN SENValues
            )
        )
    )

RETURN
    FinalCount

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[belvoir99 · Accepted solution]

@wardy912 thanks so much for your code - it almost works but errors out on the line

SENPYD[SENPupilGroup] IN SENValues

 This is the same problem I was having, namely that the SENValues single column table is produced from an IF statement which doesn't allow a table as an output. 

Some interesting ideas here though! So thanks very much!

I have now (I think, subject to final testing) found the solution - which might be of interest to you:

SSUM Test = 
VAR AllTable = VALUES(PupilYearData[PupilSchoolDataID])
VAR SexSlicer = CALCULATE( SELECTEDVALUE(PupilGroup[PupilGroupID]), PupilGroup[PupilGroupID] IN {2, 3} )
VAR SexValues = SELECTCOLUMNS( FILTER( ADDCOLUMNS( GENERATESERIES(2, 3), "NewCol", IF( ISBLANK(SexSlicer), [Value], SexSlicer) ), [Value] = [NewCol] ), "Result", [Value] )

VAR SENSlicer = CALCULATE( SELECTEDVALUE(PupilGroup[PupilGroupID]), PupilGroup[PupilGroupID] IN {4, 5} )
VAR SENValues = SELECTCOLUMNS( FILTER( ADDCOLUMNS( GENERATESERIES(4, 5), "NewCol", IF( ISBLANK(SENSlicer), [Value], SENSlicer) ), [Value] = [NewCol] ), "Result", [Value] )

VAR FinalCount = 
    IF( MIN(PupilGroup[PupilGroupID]) = 1, 
        COUNTROWS(AllTable),
        CALCULATE(
            COUNTROWS(PupilYearData),
            Sex[SexPupilGroupID] IN SexValues, 
            SENPYD[SENPupilGroup] IN SENValues
        )
    )
RETURN
FinalCount

The code is a bit too concise here (sorry) but with some formatting it'll be a bit more readable. I had to apply the technique for SENValues onto SexValues too.
Copilot and SQLBI were my friends here plus a little bit of hard thinking! 🙂
SexValues returns either (2), (3) or (2, 3) as single column table, and particularly returns (2,3) even if the two slicer values are unticked.
I took (2, 3) from GENERATESERIES (can also use DATATABLE) and then added another column which got its value from the slicer value. If the slicer value is blank (i.e. > 1 value) then put in the first column value otherwise put the slicer value.
Finally filter the rows so that only the match between first and second column are returned.