Cross highlight seems to remove affect of ALL

[jbaynhamUK]

I have a Dates table, which includes a Season column. Seasons run from August 1 to July 31. As part of a more complex measure, I need to know the last selected date in the date slicer. The measure is run per season, so to get this date I am using

MaxSelectedDate =
    CALCULATE ( MAX ( Dates[Date] ), ALL( Dates[Season] ) )

This works fine, if the slicer date range is set to end at 31 May 22, the measure returns this date for all seasons. However, the visual featuring the measure can be cross-highlighted by another season-based visual. If you choose say, the 2018/19 season, the measure calcs as 31 July 19, the max date for the chosen season. It seems to ignore the ALL( Dates[Season] ) filter removal. How can I stop it doing this? The DAX query being run is:

DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Dates'[Date])),
      'Dates'[Date] < DATE(2022, 6, 1)
    )

  VAR __DS0FilterTable2 = 
    TREATAS({"2018/19"}, 'Dates'[Season])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Dates'[Season], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Measure_2", CALCULATE ( MAX ( Dates[Date] ), ALL(Dates[Season]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Dates'[Season], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Dates'[Season]

[v-xiaotang · Accepted solution]

Hi @jbaynhamUK 

Thanks for reaching out to us.

>>  As part of a more complex measure, I need to know the last selected date in the date slicer.

you can use  date=maxx(allselected('date'),[date])  to get the last selected date in the date slicer.

>> f you choose say, the 2018/19 season, the measure calcs as 31 July 19, the max date for the chosen season. It seems to ignore the ALL( Dates[Season] ) filter removal. 

The fact is that when you select in Season, it also affects the range of the date table, so when two filters on the date table happen together, they cannot be distinguished, measure will only return the largest date after the two filters. 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[jbaynhamUK]

Thanks for this. I understand what you are saying, but it does seem crazy that the inbuilt cross filtering/highlighting functionality is able to sabotage the operation of measures in this way, with no way of writing them to protect against it.

[jbaynhamUK]

Thanks for your reply. I have tried with ALLSELECTED, but the measure still returns the last date in the season, not the last date in the slicer.

[jbaynhamUK]

I have created a minimal example here

If you pick a date in the slicer the last date shows against every season in the right-hand table. Choose a season in the left-hand table to cross filter the right-hand table, and it changes to the last date in that season.

[amitchandak]

@jbaynhamUK , for max selected you should use allselected

MaxSelectedDate =
CALCULATE ( MAX ( Dates[Date] ), ALLselected( Dates[Season] ) )