topic Re: Applying calculate with filter in DAX Commands and Tips

Applying calculate with filter

yazoubi — Tue, 28 Sep 2021 07:07:23 GMT

Hi Dears,

Can someone help me please to do a calculation for total staying in accommodation based on movement type (check-in/check-out) and consider the other filter like per user number and per requester of movement type

- The logic will be:

Total_Stay = (date and time for check out) - (date and time for check-in) per User, per requester, and per accommodation

below is an example of the data:

date and time	Date	User	Accommodation	Movement type	Requester
9/23/2021 16:12	9/23/2021	User 1	ACC1	Check-In	Requester 1
9/25/2021 16:53	9/25/2021	User 1	ACC1	Check-Out	Requester 1
9/23/2021 16:12	9/23/2021	User 2	ACC1	Check-In	Requester 3
9/25/2021 16:53	9/25/2021	User 2	ACC1	Check-Out	Requester 3

Thanks and looking forward to your support.

Re: Applying calculate with filter

amitchandak — Tue, 28 Sep 2021 07:50:43 GMT

yazoubi , Try a new measure like

Datediff(calculate(min(Table[Date Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] ="Check-In")),4calculate(Max(Table[DAte Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] =" Check-Out")), day)

Re: Applying calculate with filter

yazoubi — Tue, 28 Sep 2021 08:28:49 GMT

Dear amitchandak ,

Thanks for your reply and note the below error, if you can assist me please

Re: Applying calculate with filter

amitchandak — Tue, 28 Sep 2021 09:43:57 GMT

yazoubi , parenthesis are wrong place , Please check my doubts

Datediff(
calculate(min(Table[Date Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] ="Check-In"))
, calculate(Max(Table[DAte Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] =" Check-Out"))
, day)

Or Paste the formula in text format

Re: Applying calculate with filter

yazoubi — Tue, 28 Sep 2021 09:54:25 GMT

Hi amitchandak ,

The formula in text format as below

Total_Stay = DATEDIFF(CALCULATE(MIN('Accommodation Transaction'[Created date and time]),ALLEXCEPT('Accommodation Transaction','Accommodation Transaction'[Requester],'Accommodation Transaction'[Accommodation],'Accommodation Transaction'[Personnel number]), FILTER('Accommodation Transaction','Accommodation Transaction'[Movement type] ="Check-In")),calculate(Max('Accommodation Transaction'[Created date and time]), allexcept('Accommodation Transaction','Accommodation Transaction'[Requester],'Accommodation Transaction'[Accommodation],'Accommodation Transaction'[Personnel number]), filter(FILTER('Accommodation Transaction','Accommodation Transaction'[Movement type] ="Check-Out"), day)))