Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Using Slicer Selection as Dynamic Variable in Filter Function

    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
    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

    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

    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

    Posted 29 days ago
    Edited by TODD BENSON 29 days ago
    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
    ------------------------------