topic yet another allexcept question in DAX Commands and Tips

yet another allexcept question

fsim — Wed, 19 May 2021 09:39:54 GMT

hi !

I wanted to get the first TimeSheet date encoded by every employee.

mindate = CALCULATE(MIN((TS[msdyn_date])), ALLEXCEPT(TS,TS[msdyn_bookableresource]))

this work perfectly.

then I also wanted to keep a calendar filter. So I added TS[msdyn_date] to the ALLEXCEPT -->

mindate = CALCULATE(MIN((TS[msdyn_date])), ALLEXCEPT(TS,TS[msdyn_bookableresource],TS[msdyn_date]))

I expected that any filter applied to the Date table would affect my mindate. It doesn't.

The Calendar table is linked to the msdyn_date and I put a slicer on it.

deit: I tried something else :

mindate = CALCULATE(MIN((TS[msdyn_date])), ALLEXCEPT(TS,TS[msdyn_bookableresource], 'Date'[YearMonthShort]))
this worked, but I cannot accept that solution because the slicer will not be the only way to filter dates 😕

Re: yet another allexcept question

OwenAuger — Wed, 19 May 2021 21:05:18 GMT

Hi fsim

ALLEXCEPT can take a mixture of column & table references for arguments 2+.

So you could write this to ensure any filters from the 'Date' table are retained:

mindate = CALCULATE ( MIN ( TS[msdyn_date] ), ALLEXCEPT ( TS, TS[msdyn_bookableresource], 'Date' ) )

Does this achieve the result you are wanting?

Regards,

Owen

Re: yet another allexcept question

fsim — Wed, 19 May 2021 11:54:44 GMT

OwenAuger ,

it worked.
I don't know why I wanted to add a column instead of a full table. Thank you !