topic Re: need to find details related to latest date in Policy based on date selected in the Slicer. in DAX Commands and Tips

need to find details related to latest date in Policy based on date selected in the Slicer.

Anonymous — Fri, 29 Apr 2022 13:53:26 GMT

Hi Guys, I am facing an issue in getting a solution for finding details related to the latest date in policy based on date range selected using the slicer.

Date used. -

I want to get details related to the latest date when the status is "In Progress" for policy x.

I have made a measure flag

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLEXCEPT(Sheet1,Sheet1[Policy])),1,0)

this is working fine when the complete date range is selected in the slicer means till 5 June 2022 but when I reduce the slicer range it is not giving data corresponding to 1 Feb 2021 the table is getting blank. Instead, it is giving values related to both 1 Feb 2021 and 4 May 2020. I want only value corresponding to 1 Feb 2021 when Slicer is moved to left a little.

I want the flag to be dynamic based on slicer range.

I am attaching the file PBIX file.

Re: need to find details related to latest date in Policy based on date selected in the Slicer.

amitchandak — Fri, 29 Apr 2022 15:55:02 GMT

Anonymous ,

try like

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLSELECTED(Sheet1[Policy])),1,0)

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLSELECTED()),1,0)

Re: need to find details related to latest date in Policy based on date selected in the Slicer.

Anonymous — Fri, 29 Apr 2022 16:25:18 GMT

Above code is not working.

Re: need to find details related to latest date in Policy based on date selected in the Slicer.

v-yalanwu-msft — Wed, 04 May 2022 06:51:02 GMT

Hi, Anonymous ;

Modify it.

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),FILTER(ALLSELECTED(Sheet1),[Policy]=MAX('Sheet1'[Policy]))),1,0)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.