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 nwitstine,

 

I don't have access to your linked file. 

1. If you'd like to filter the table in the Query Editor, you need to create it in the Query Editor. 

2. Why do you want to filter in the Query Editor?

3. What should the values of the column be?

 

 

Best Regards,
Dale

nwitstine's avatar
nwitstine
Frequent Visitor
7 years ago

v-jiascu-msft I shared the doc with you.

 

I updated the dataset to highlight the rows that I'd like to show after the filter is applied (i.e. white should be filtered out, yellow should remain). It should also be noted that while the "most recent" date in this sample dataset is 12/1/2016 for all properties, for my larger dataset that's not the case. The most recent date depends on when properties are bought, sold, and developed. 

 

I understand the need to filter in the query editor, but I haven't found a solution for how it can be done. When I filter for most recent date, it finds the most recent line item and doesn't have a way to apply the "by property code" constraint that I need it to.

 

I have read through several solutions on this and most involve creating a calculated column followed by a custom measure - I haven't gotten any of these to work with my data, and as I said, it would be preferable to filter out in the query editor.

 

Thanks for your help,

Nicole

  • 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

    • nwitstine's avatar
      nwitstine
      Frequent Visitor
      7 years ago

      v-jiascu-msft The first solution doesn't work (for this sample yes) because my entire dataset is not this uniform - the dates vary a lot.

       

      I ended up going with the second solution - I wasn't able to use your formula when creating a custom column, but I was able to follow the advanced editor, from what you shared, to insert this into my table steps (Note: my previous step was "Added Refresh Date"):

       

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

      in

      #"Added Date Filter"

       //    (let currentProperty = [Property Code] in Table.SelectRows(#"Added Date Filter", each [Property Code] = currentProperty))[Area Date]

       

      I'm not exactly sure what the function of the last // clause is - it seems like I can remove it without affecting my table. 

       

      This works for now, but I would be interested in finding more ways to filter by date as I have more complicated calculations that I eventually want to apply based on when a row was entereing into a table. (i.e. multidate filters).

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

        Hi nwitstine,

         

        Sorry for the confusion. That's just a note. You can delete it. If you have more requirements, please feel free to post here.

         

         

        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.