Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
4 years ago
Solved

Filter table with calculated table

Hello, 

my original table:

 

Category  Date Name
A 25.01.2022 Pan
B 25.01.2022 Hook
A 24.01.2022 Hook
B 24.01.2022 Pan 
A 23.01.2022 Pan 
B 23.01.2022 Hook

 

How to make a calculated table which in any case always only shows the newest date for both categories ? 

 

Expected outcome like this:

Category  Date Name
A 25.01.2022 Pan
B 25.01.2022 Hook

 

I tried addcolumns with summarize and calculated max for date and name. It might works for date but somehow there is no context tranisition for the name. Instead it will show the name with the "higher" alphabet.  The name can be wrong. 

Thank you very much in advance.

Best. 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Applicable88 
    For table you can use

    Last Data = 
VAR DataWithLastDatePerCategory =
    ADDCOLUMNS ( 
        Data,
        "@LastDate",
        VAR CategoryTable =
            CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
        RETURN
            MAXX ( 
                CategoryTable,
                Data[Date]
            )
    )
VAR Result =
    FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
    SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )

     

    for Calculated column you can use

    Name On Last Date = 
VAR CategoryTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
    MAXX ( 
        CategoryTable,
        Data[Date]
    )
VAR Result =
    MAXX (
        FILTER ( CategoryTable, Data[Date] = MaxDate ),
        Data[Name]
    )
RETURN
    Result
  • BeaBF's avatar
    BeaBF
    4 years ago

    Applicable88 Try with:

     

    = FILTER (
    Tabella,
    Tabella[Date]
    = CALCULATE (
    MAX ( Tabella[Date] ),
    ALLEXCEPT ( Tabella, Tabella[Category ] )
    )
    )
     
    B.

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Applicable88 
    For table you can use

    Last Data = 
VAR DataWithLastDatePerCategory =
    ADDCOLUMNS ( 
        Data,
        "@LastDate",
        VAR CategoryTable =
            CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
        RETURN
            MAXX ( 
                CategoryTable,
                Data[Date]
            )
    )
VAR Result =
    FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
    SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )

     

    for Calculated column you can use

    Name On Last Date = 
VAR CategoryTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
    MAXX ( 
        CategoryTable,
        Data[Date]
    )
VAR Result =
    MAXX (
        FILTER ( CategoryTable, Data[Date] = MaxDate ),
        Data[Name]
    )
RETURN
    Result
  • BeaBF's avatar
    BeaBF
    Super User
    4 years ago

    Applicable88 Try with:

     

    = FILTER (
    Tabella,
    Tabella[Date]
    = CALCULATE (
    MAX ( Tabella[Date] ),
    ALLEXCEPT ( Tabella, Tabella[Category ] )
    )
    )
     
    B.