How to filter to show the last car park/visit in a specific period

[jr3151006]

Hi,

just to explain scenario, let's say that we have a CAR PARKING application to register entrances.

The current main table, has '165' records and most of these 'entries' are for the same car (license plate or just 'tag').

When I use the 'Slicer' (which uses a 'DateDimension' table with a range of dates where it also has relationship with main table using 'Date' fields) in order to filter the records for a specific period desired (Nov/2011, Dec/2011 and Jan/2022 in that case), my 'table' than resumes itself with only '85' records.

Since it's not 'grouping' records based on 'licenseplate' field, it´s correct so far.

#################################################

#                 MAIN TABLE                        #

#################################################

LICENSEPLATE        |     NAME        |          DATE        #

#################################################

1203234                |  JOHN                | 01/Sep/2021

1203234                |  JOHN                | 05/Oct/2021

1203234                |  JOHN                | 26/Nov/2021

1203234                |  JOHN                | 15/Dec/2021

1203234                |  JOHN                | 05/Feb/2022

1203234                |  JOHN                | 02/Mar/2022

9876544                |  JIM                | 06/Apr/2020

9876544                |  JIM                | 01/May/2021

9876544                |  JIM                | 22/Dec/2021

9876544                |  JIM                | 07/Jan/2022

9876544                |  JIM                | 05/Feb/2022

9876544                |  JIM                | 02/Mar/2022

5656239                |  KEVIN        | 07/Feb/2021

5656239                |  KEVIN        | 05/Apr/2021

5656239                |  KEVIN        | 01/May/2021

5656239                |  KEVIN        | 26/Nov/2021

5656239                |  KEVIN        | 05/Fev/2022

5656239                |  KEVIN        | 02/Mar/2022

>>>>>>>

PROBLEM

>>>>>>>

If I edit the table to add a step to perform a 'grouping' by the 'LICENSEPLATE' field based on 'DATE' field using 'MaxDate' function in the query, my table will shows only 03 records of 'Mach/2022' - which is the lastest for each car on the table.

As you can see (or imagine), the LICENSEPLATE field was filtered and grouped correctly, but the 'MaxDate effect' give me a wrong records for each car!

>>>>>>>>>>>>>>>

DESIRED RESULTS

>>>>>>>>>>>>>>>

Since the desired period is 'Nov/2011', 'Dec/2011' and 'Jan/2022', the expected results should be only these:

#################################################

#                 MAIN TABLE                        #

#################################################

LICENSEPLATE        |     NAME        |          DATE        #

#################################################

5656239                |  KEVIN        | 26/Nov/2021 <<<

1203234                |  JOHN                | 15/Dec/2021 <<<

9876544                |  JIM                | 07/Jan/2022 <<<

How to adjust the Query on the table for to do that and than use its contents/results on the pages objects (graphs, cards and etc)?

[jr3151006]

Here is the .PBI file:
CarParking.pbix

[TomMartens]

Hey @jr3151006 ,

thanks for the sample files.

Before I explain the steps that lead to this solution: 

It's important to remember that a dynamic table, dynamic in the sense that it reflects the current filter context, is not possible and can not be created. DAX-based tables will only be calculated when the data model is refreshed. This means it is not possible to create the table "Table - with groupby."
Virtual tables (tables created inside a DAX measure) reflect the current filter context.
All this means, it's only possible to filter an existing table accordingly, here it's the table - Table.

The measure I created for the filtering:

flagLastDate = 
var filtertable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table'
            , 'Table'[LicensePlate]
        )
        , "MaxDate" , CALCULATE( MAX( 'Table'[Date] ) , ALLSELECTED( 'Table'[Date]) )
    )
return

COUNTX(
    'Table'
    , IF( CONTAINSROW( filtertable , 'Table'[LicensePlate] , 'Table'[Date] )
        , 1
        , BLANK()
    )
)

This measure has two parts

1. create a table "filtertable" that contains only the license plates with the MAX date considering the current selection from the DateDimension table
2. Checking if the current row from the table Table is part of the filtertable. This check is done using the CONTAINSROW function inside an IF. If the current row or being more precise only the license plate and the date of the current row are part of the filtertable, then 1 is returned, otherwise BLANK(). As no specific aggregate function is required I use COUNTX.

This measure can be used as filter on the table visual - a visual level filter:

Hopefully, this provides what you are looking for.

Regards,

Tom

[TomMartens]

Hey @jr3151006 ,

this is a more complex request, as the last date per plate will vary due to the selected date range.
you have to create a measure that returns 1 for a date that is the max date for each plate, then you can use this measure on the visual, this will then show only the rows that are matching.
Please take the time to create a pbix that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Explain the expected result based on the data you provide.

Regards,

Tom

[KNP]

I think, if I understand correctly, you need to add a month column to your data, and include it in the grouping columns.

That way, your MAX date will get the max for the new Month column context as well, not over all months.

[KNP]

I didn't read the filtering portion of your question well enough.

@TomMartens is correct, you need the measure so that it is calculated on the fly.

Create a sample PBIX and share it as Tom suggested. You will get an answer much quicker that way.

[jr3151006]

Regarding the filter, the main goal is answering the following question:
"- Who was the employee (last record) visit the company and access the park area on a specific date interval?"


* I'm preparing a .PBI to publish here.

[jr3151006]

Could I use a new calculated collumn for that table, and then compare something like:
 IF('Table1'[Date]=SlicerInterval,1,0)?

After that calc, I could filter?

[jr3151006]

After some tests and research, I found that the 'Latest' feature on the columns, applied directly to the main 'TABLE' dataset (without need to use 'groupby' feature), shows the desired results.

🤔
* Now the research continues in a way to maintain that behavior directly on the table - or even on a new 'virtual table' created via DAX.