Forum Discussion

Pierre_B's avatar
Pierre_B
New Member
3 years ago
Solved

Create a max date measure for each id

Hi everyone,

 

I would like to create a measure using dax in order to get the max date for each id and filtered date, and keeping the every date in my table.

 

dateidmax date
01/01/2022A01/03/2022
01/02/2022A01/03/2022
01/03/2022A01/03/2022
01/01/2022B01/03/2022
01/02/2022B01/03/2022
01/03/2022B01/03/2022
01/01/2022C01/02/2022
01/02/2022C01/02/2022
01/01/2022D01/03/2022
01/03/2022D01/03/2022

 

dateidmax date
01/01/2022A01/02/2022
01/02/2022A01/02/2022
01/01/2022B01/02/2022
01/02/2022B01/02/2022
01/01/2022C01/02/2022
01/02/2022C01/02/2022
01/01/2022D01/01/2022

 

Two examples of tables I would like (they are the same, the second one is filtered).

 

I tried multiple formula, such as:

max date =
var id = SELECTEDVALUE(table[id])
return maxx(filter(table, ALLSELECTED(table[Date]) && table[id]=sec), table[id])
 
Bu it didn't work.
 
If someone has an idee, it would be very helpful. 
 
Thank you,
Pierre
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    Pierre_B,

    Try using a variable. For me that seems to do the trick.  

     

     

    max date = 
VAR _dates =
    ALLSELECTED ( 'Table'[date] )
VAR _result =
    CALCULATE (
        MAX ( 'table'[date] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[id] ),
        _dates
    )
RETURN
    _result

     

     

     

8 Replies

  • Barthel's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hey Pierre_B,

    Give this a try:

    max date = 
CALCULATE ( 
    MAX ( 'table'[date] ),
    ALLEXCEPT ( 'table', 'table'[id] )
)

    It calculates the maximum date for each row removing all fitler context of the table, except that of the 'id', so that the maximum date is calculated per 'id'.

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    3 years ago
    Hi !
    Pierre_B 
    Use this one
    Measure=Calculate(max(Date),Allexcept(Tablename,IDColumn))

     Thanks,
    Thennarasu R

    • Pierre_B's avatar
      Pierre_B
      New Member
      3 years ago

      Hi Thennarasu_R,

       

      As replied to Barthel, it doesn't take into account the filter on 'Date'.

       

      Thank you for your response. I am not getting what is expected, as I would like to have the max date of the selected dates. So I need to keep the filter on 'Date'.




      In my example (2), I filtered 'Date' to keep Jan & Feb and the 'max date' showed Feb as max date for ID A, B, C and Jan for D. 




      With ALLEXCEPT, I get the max date among all 'Date'. 




      Let me know if you have any ideas.

       

  • Pierre_B's avatar
    Pierre_B
    New Member
    3 years ago

    Hi Barthel, 

     

    Thank you for your response. I am not getting what is expected, as I would like to have the max date of the selected dates. So I need to keep the filter on 'Date'.

     

    In my example (2), I filtered 'Date' to keep Jan & Feb and the 'max date' showed Feb as max date for ID A, B, C and Jan for D. 

     

    With ALLEXCEPT, I get the max date among all 'Date'. 

     

    Let me know if you have any ideas.

    • Barthel's avatar
      Barthel
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Pierre_B,

      Oh yes I see what you mean. You can make the calculation take into account the selected dates by using the ALLSELECTED formula. This removes the filter context from within the visual (the rows of the matrix), and keeps the filter context from outside the visual (for example, a slicer). 

       

      max date =
CALCULATE (
    MAX ( 'table'[date] ),
    ALLEXCEPT ( 'table', 'table'[id] ),
    ALLSELECTED ( 'table'[date] )
)

       

      Does this work?

  • Pierre_B's avatar
    Pierre_B
    New Member
    3 years ago

    This is what I tried but I get this message: 

    "Expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

     It is always what I get when I combine filter on selected dates and IDs...

    • Barthel's avatar
      Barthel
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Pierre_B,

      Try using a variable. For me that seems to do the trick.  

       

       

      max date = 
VAR _dates =
    ALLSELECTED ( 'Table'[date] )
VAR _result =
    CALCULATE (
        MAX ( 'table'[date] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[id] ),
        _dates
    )
RETURN
    _result