Latest date across two tables using DAX

[SJHA]

I have two tables:

Project:

[Project_title] [Project_module]

A                     A

A                     B

A                     C

B                     D

B                     E

C                     F

Usage:

[Project_module] [Latest_date]

A                     01-02-23

B                     02-02-23

C                     03-02-23

D                     01-02-23

E                     02-02-23

F                     04-02-23

Relation between the tables is [Project_module].

I would like to look at the latest date but on project title level. How can I use DAX to do that? I've been trying MAXX and I can't seem to figure it out.

[Anand24 · Accepted solution]

Hi @SJHA ,
Try the below calculated measure:

Title Level Latest Date = 
CALCULATE(
    MAX(Usage[Latest Date]), 
    ALLEXCEPT(Project, Project[Project Title])
)

Here's the output:

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[SJHA]

@Anand24 

I have 3 tables: Project, Module and Usage

Currently it looks like the table in the lower right corner, or if I removed the 'module' column it just repeats the project with different dates.

I would like it to be the project name once for the latest date.

[Anand24 · Accepted solution]

Hi @SJHA ,
The measure I mentioned earlier would still work:

Can you change the relationship to below?

If relationship can't be shared, can you share the current relationship between these 3 tables please.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Ahmedx]

measure = 
MAXX( 
FILTER(AL('Usage'),
    'Usage'[Project_module]= max('Project'[Project_module]),[Latest_date]
)

----
Colum = 
MAXX( 
FILTER(AL('Usage'),
    'Usage'[Project_module]= 'Project'[Project_module],[Latest_date]
)

[SJHA]

@Anand24 

I just found out that the relation is not as I mentioned.

The relation is through another table:

Project relation is Module parent_ID

Usage relation is Module ID

Is it still possible?

[Anand24]

@SJHA ,
We should be able to solve it using DAX but I'm not exactly able to identify the table and relationship in the reply.
Is it possible for you to create a dummy .pbix file and share? Or can you share the table info just like you shared in original post?

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Anand24 · Accepted solution]

Hi @SJHA ,
Try the below calculated measure:

Title Level Latest Date = 
CALCULATE(
    MAX(Usage[Latest Date]), 
    ALLEXCEPT(Project, Project[Project Title])
)

Here's the output:

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[SJHA]

@Anand24 
Its doesn't work quite as you've shown.

What might have happened here?

[Anand24]

@SJHA ,
Can you try adding CROSSFILTER function in your query? Currently the filtering is not getting hit since the relationship is uni-directional.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn