Forum Discussion
Manipulating slicer values with single column tables
wardy912 thanks so much for your code - it almost works but errors out on the line
SENPYD[SENPupilGroup] IN SENValuesThis 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 FinalCountThe 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.
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!
wardy912 thanks so much for your code - it almost works but errors out on the line
SENPYD[SENPupilGroup] IN SENValuesThis 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
FinalCountThe 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.