Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return max date given criteria

Hello, I have a table of data that look like the below: Dates Area To display? 01-Jan-20 1 No 02-Jan-20 1 No 03-Jan-20 1 No 04-Jan-20 1 No 05-Jan-20 1 Yes 06-Jan-20...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    How about this?

    MAX_DATE =
CALCULATE (
    MAX ( YESTABLE[Dates] ),
    ALLEXCEPT ( GEOGRAPHY, GEOGRAPHY[AREA] ),
    YESTABLE[To display] = "Yes"
)

     

    If it doesn't work, please share us some sample data of your two tables, removing sensitive information.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

I would use

MAX_DATE = CALCULATE(MAX(table[Dates]),FILTER(ALLEXCEPT(table,table[Area]),table[To display] = "Yes")

and plot it using matrix

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you. My issue is that Table[area] isn't part of the Table. "Area" comes from another table and is brought here through a relationship. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous 

       

      In that case you need to create a relationship between both tables.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The relationship has already been created, so how should I re-write this part of the code?

        FILTER(ALLEXCEPT(YESTABLE, GEOGRAPHY[AREA]) doesn't seem to work.

        FILTER(ALLEXCEPT(table,table[Area]