topic Re: Max Date by Condiction in DAX Commands and Tips

Max Date by Condiction

LD — Tue, 28 Apr 2020 08:03:08 GMT

Dear,

I have the following situation:

Raw Data

Column 1 Column 2 Column 3

ID 1 Task 1 Due Date A

ID 1 Task 2 Due Date B

ID 2 Task 1 Due Date C

ID 3 Task 1 Due Date D

ID 3 Task 2 Due Date D

ID 3 Task 3 Due Date E

ID n ...... ......

I need to select the due max due date, excluding the task 2 and considering the Column 1.

The result is the association of Max due date to ID (1,2,3, n) excluding some condiction (by Column 2).

Thank you!!!!

Re: Max Date by Condiction

camargos88 — Tue, 28 Apr 2020 11:25:46 GMT

Hi LD ,

Try this measure:

Measure = CALCULATE(MAX('Table'[Column 3]); ALLEXCEPT('Table'; 'Table'[Column 1]))

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

Re: Max Date by Condiction

LD — Tue, 28 Apr 2020 13:26:38 GMT

Dear camargos88 ,

Command is quite ok..., Please, I need to exclude some row based on the Column2 (for example Task2 in Cl2)

Thank You

Re: Max Date by Condiction

camargos88 — Tue, 28 Apr 2020 13:30:53 GMT

LD ,

I didn't get what you want.

Can you explain more ?

Ricardo

Re: Max Date by Condiction

LD — Tue, 28 Apr 2020 13:46:58 GMT

Yes, I'm sorry...

For example, I need to exclude (in ID3) the Due Date E because coming from Task3...

so all Task3 in every ID(1,2,3, n) will be not selected to determinate the max Due Date.

In the Example, the Due date should be "Due Date D"

I hope I was clear 🙂

Thank you!!!!

Re: Max Date by Condiction

camargos88 — Tue, 28 Apr 2020 13:55:00 GMT

LD ,

Can you post the expected result ?

Ricardo

Re: Max Date by Condiction

LD — Tue, 28 Apr 2020 14:40:11 GMT

Hi camargos88 ,

Yes:

Column1	Column2	Due Date	Measure
ID1	Task Type A	Jan 31, 20	March 31, 20
ID1	Task type B	Mar 31, 20	March 31, 20
ID2	Task Type A	Aug 22,20	Aug 22,20
ID3	Task Type A	Apr 18, 20	Apr 18, 20
ID3	Task Type C	Jun 20, 20	Apr 18, 20
ID4	Task Type A	Apr 18, 20	May 05,20
ID4	Task Type B	May 05,20	May 05,20
ID4	Task Type C	Jun 15, 20	May 05,20

so the task type C, is not considered into max value

Thank you

Re: Max Date by Condiction

camargos88 — Tue, 28 Apr 2020 14:44:05 GMT

LD ,

Try this measure:

Measure = CALCULATE(MAX('Table'[Due Date]); FILTER(ALLEXCEPT('Table'; 'Table'[Column1]); 'Table'[Column2] <> "Task Type C"))

Ricardo