Forum Discussion

nwitstine's avatar
nwitstine
Frequent Visitor
7 years ago
Solved

Latest Date Filter

I have seen many solutions to this problem, but none of them seem to work for my dataset. I have a table listing property names, codes, area, and area date. I'd like to filter my table to only show o...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Nicole,

     

    There could be three solutions. Please check out you Messages box and download the demo.

    1. If every property has all the same dates, you can filter them directly. Please refer to the snapshot below.

    Latest-Date-Filter1

    2. Add a column in the Query Editor and filter.

     

    if [Area Date] = List.Max(let currentProperty = [Property Code] 
    in Table.SelectRows(#"Changed Type", 
    each [Property Code] = currentProperty)[Area Date]) 
then 1 
else 0

    3. Use DAX formula.

     

     

    Table =
FILTER (
    ADDCOLUMNS (
        'Table3',
        "IfMax", CALCULATE (
            MAX ( Table3[Area Date] ),
            FILTER ( 'Table3', 'Table3'[Property Code] = EARLIER ( Table3[Property Code] ) )
        )
    ),
    [IfMax] = [Area Date]
)

     

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Nicole,

 

There could be three solutions. Please check out you Messages box and download the demo.

1. If every property has all the same dates, you can filter them directly. Please refer to the snapshot below.

Latest-Date-Filter1

2. Add a column in the Query Editor and filter.

 

if [Area Date] = List.Max(let currentProperty = [Property Code] 
    in Table.SelectRows(#"Changed Type", 
    each [Property Code] = currentProperty)[Area Date]) 
then 1 
else 0

3. Use DAX formula.

 

 

Table =
FILTER (
    ADDCOLUMNS (
        'Table3',
        "IfMax", CALCULATE (
            MAX ( Table3[Area Date] ),
            FILTER ( 'Table3', 'Table3'[Property Code] = EARLIER ( Table3[Property Code] ) )
        )
    ),
    [IfMax] = [Area Date]
)

 

 

Best Regards,
Dale

K_Baathe's avatar
K_Baathe
Frequent Visitor
4 years ago

Thank you so much v-jiascu-msft, the second solution works great!

You have saved me so much time with this! 🙂

 

I have a question about the third solution, which I would really like to use for another context:
I get the error:
"DAX comparison operations do not support comparing values of type Date with values of type Text."
which i believe is what is being done by the MAX function in my case. (I'm not having a property code, but a System ID, which is in text-format.) Even if you have a numerical value, it feels wrong to do MAX of the DATE and the Property Code/System ID. 
Or am I misunderstanding something? If you could fix the DAX, I would be very interested in what that would look like, since I'm trying to get the hang of EARLIER and such expressions.