Using Slicer Selection as Dynamic Variable in Filter Function

Using Slicer Selection as Dynamic Variable in Filter FunctionJump to Best AnswerFollow
TODD BENSON
TODD BENSONOct 26, 2022 03:07 PM
I have this MAXX formula that has a nested filter in it. Part of the filter is date-driven, and as you …
1. Using Slicer Selection as Dynamic Variable in Filter Function

TODD BENSON
Posted Oct 26, 2022 03:07 PM
I have this MAXX formula that has a nested filter in it.

Part of the filter is date-driven, and as you can see below the comparison date is hard-coded into the formula and returns the last day of the month: EOMONTH(DATE(2022,6,1),0).

I would like to replace the hard-coded date above with the variable titled Selected_Month. As you observe below the variable is to be populated with a selected last day of the month date from a Year/Month slicer combination: SELECTEDVALUE(‘DateDimension EndDate'[EOM]). The slicer is base on a static date table.

However, when I replace EOMONTH(DATE(2022,6,1),0) with SELECTEDVALUE(‘DateDimension EndDate'[EOM]) I get no results. I may be approaching this wrongly. Might this be possible?
The Code:
MaxDate =
VAR CurrentName = ‘CriteriaReceived'[indexPGC]
VAR Selected_Month = SELECTEDVALUE(‘DateDimension EndDate'[EOM])

RETURN
MAXX(
FILTER( ALL(‘CriteriaReceived’),’CriteriaReceived'[indexPGC] = CurrentName
&& EOMONTH(‘CriteriaReceived'[Created],0) <= EOMONTH(DATE(2022,6,1),0)
),
‘CriteriaReceived'[Created]
)

——————————
TODD BENSON
——————————

2. RE: Using Slicer Selection as Dynamic Variable in Filter Function

Bronze Contributor
Tomas Torp
Posted Oct 26, 2022 03:45 PM
The variable CurrentName shows that you have a row context outside of MAXX, so that must mean that this is a calculated column, not a measure, right?

The thing about calculated columns is that they are only calculated at dataset refresh, so they can’t respond to any slicers in any of the reports connected to the model. On the other hand, if you rewrite it as a measure it could respond to the slicer.

——————————
Tomas
——————————

3. RE: Using Slicer Selection as Dynamic Variable in Filter Function

TODD BENSON
Posted Oct 31, 2022 10:57 AM
Thank you Tomas. I will continue to investigate other solutions. if I find anything I will post back in this thread for others to learn.

——————————
TODD BENSON
——————————

4. RE: Using Slicer Selection as Dynamic Variable in Filter Function

Kaz Shakir
Posted Oct 31, 2022 12:41 PM
@TODD BENSON,
How does your data model look for this dataset? In particular, what is the relationship between the ‘CriteriaReceived’ table and the ‘DateDimension EndDate’ table? It would be very helpful if you could share a .pbix file with some “dummy data” so that we could see the whole model.

Kaz.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

5. RE: Using Slicer Selection as Dynamic Variable in Filter Function Best Answer

TODD BENSON
Posted Nov 03, 2022 11:06 AM
Edited by TODD BENSON Nov 03, 2022 11:11 AM
I made a breakthrough! I built 3 measures to step into a solution (probably a more efficient way to do this, but this works for me)

Measure 1 determines if the data row’s ReceivedDate is <= the month/year slicers that are based on the ‘DateDimension EndDate'[EOM] field
mFilteredReceivedDate =
VAR vDateReceived = SELECTEDVALUE(CriteriaReceived[ReceivedDate])
VAR vDateFiltered = SELECTEDVALUE(‘DateDimension EndDate'[EOM])

RETURN
IF(DATEDIFF(vDateFiltered,vDateReceived,DAY)<=0,1,0)​
Measure 2 determines the max date amongst the rows where mFilteredReceivedDate = 1

mMaxDate =
VAR vIndexPC = MAX(CriteriaReceived[indexPC])
VAR vFiltered = CriteriaReceived[mFilteredReceivedDate]
VAR vDateFiltered = SELECTEDVALUE(‘DateDimension EndDate'[EOM])

RETURN

MAXX(FILTER(ALL(CriteriaReceived),CriteriaReceived[indexPC]=vIndexPC && vFiltered=1 && CriteriaReceived[ReceivedDate] <= vDateFiltered),CriteriaReceived[ReceivedDate])
Measure 3 compares the ReceivedDate field with the mMaxDate measure and matches are marked with a 1

mMaxDateSelected =
VAR vDateReceived = SELECTEDVALUE(CriteriaReceived[ReceivedDate])
VAR vMaxDate = [mMaxDate]
VAR vFiltered = CriteriaReceived[mFilteredReceivedDate]

RETURN
IF(DATEDIFF(vDateReceived,vMaxDate,DAY)<=0 && vFiltered=1 && NOT(ISBLANK(vMaxDate)),1,0)
Finally, I can apply a filter to my visual to show only data where mMaxDateSelected =1

——————————
TODD BENSON
——————————