Forum Discussion

SJHA's avatar
SJHA
Icon for Helper I rankHelper I
3 years ago
Solved

Latest date across two tables using DAX

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.

Need Help
  • Anand24's avatar
    Anand24
    3 years ago

    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
  • Anand24's avatar
    Anand24
    3 years ago

    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

     

8 Replies

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      SJHA
      Icon for Helper I rankHelper I
      3 years ago

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

       



       

       

       

      What might have happened here?

      • Anand24's avatar
        Anand24
        Icon for Super User rankSuper User
        3 years ago

        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

         

  • SJHA's avatar
    SJHA
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      Anand24
      Icon for Super User rankSuper User
      3 years ago

      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
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago
    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's avatar
    SJHA
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      Anand24
      Icon for Super User rankSuper User
      3 years ago

      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