Trying to count rows where the timestamp matches the latest one for each ID on or before the select

[svniekerk]

Good Day,

I have been trying to figure out the below dax statement for the past 20 hours. I have made some headway but not working as required.

I want to write a dax measure that returns all data where the following is true:

• Selected Date Context:

  • The measure should consider data for the selected date and all dates before it.

• Latest Timestamp per ID(conformed_data_id):

  • For each unique ID, identify the latest timestamp that occurs on or before the selected date. So if I choose for example 24th of September I want to see all the data for the 24 and before that. If no data for a specific id was changed on the 24th the measure needs to go and find the last entry before the 24th where the status was equal to 2.

• Status Check:

  • Check if the status associated with the latest timestamp is equal to 2.

• Count of Valid IDs:

  • Count only the IDs that meet the above conditions.

• Filters:

  • Ensure that other report or visual-level filters do not interfere with the logic.
  • Reset filters on dimensions or tables where necessary to calculate accurately.

My relationships between the 2 tables are as follow:

public fact_document_history - date_id

public dim_date - date_id 

From my limited expereince it looks like the following parts are working as required:

1). FilteredFactoryHistory

2).LastTimeStamp

It breaks as soon as I add the ValidIds part.

Any help would really be appreciated.

Kind Regards

Stephan

https://docs.google.com/spreadsheets/d/1PCJi29SuYj4iOWqEMpwwbuB7tCqGkudd/edit?usp=drive_link&ouid=11... 

Test_ValidIDs =
VAR SelectedDate =
    MAX ( 'public dim_date'[date] ) -- Capture the selected date
VAR FilteredFactHistory =
    FILTER (
        ALL ( 'public dim_date' ),
        -- Remove any existing filters from the dim_date table
        'public dim_date'[date_id] <= SelectedDate -- Filter records from fact_document_history on or before the selected date
    )
VAR LatestTimestamp =
    CALCULATE (
        MAX ( 'public fact_document_history'[event_timestamp] ),
        -- Get the latest event_timestamp from the filtered fact_document_history table
        FilteredFactHistory -- Apply the filtered context
    )
VAR ValidIDs =
    FILTER (
        'public fact_document_history',
        -- Apply filter on the fact_document_history table
        'public fact_document_history'[event_timestamp] = LatestTimestamp -- Match the latest event_timestamp
            && 'public fact_document_history'[document_status_id] = 2 -- Ensure document_status_id is 2
    )
RETURN
    COUNTROWS ( ValidIDs )
-- Return the row count for the filtered IDs

[Fowmy]

@svniekerk 

I am not sure If I got the question right:

Test_ValidIDs =
VAR SelectedDate =
    MAX ( 'public dim_date'[date_id] )
VAR LatestTimestamp =
    CALCULATE (
        MAX ( 'public fact_document_history'[event_timestamp] ),
        'public dim_date'[date_id] <= SelectedDate,
        REMOVEFILTERS ( 'public fact_document_history' )
    )
VAR ValidIDs =
    FILTER (
        'public fact_document_history',
        'public fact_document_history'[event_timestamp] = LatestTimestamp
            && 'public fact_document_history'[document_status_id] = 2
    )
RETURN
    COUNTROWS ( ValidIDs )

[svniekerk]

@Fowmy Hi. Gave you access to the data as requested.  Appreciate the assistance 

[svniekerk]

Good Day @Fowmy,

Thank you for your reply. I tested it and it returns only data from the current day no historical data.

Please let me know if you have any other questions around my requirement. I tried to give an as in dpth answer as possible.

Kind Regards

Stephan

[DataNinja777]

Hi @svniekerk ,

 To create the desired DAX measure, we need to ensure that the logic captures the selected date context, filters data for the selected date and earlier, identifies the latest timestamp for each unique ID, and verifies that the corresponding status equals 2. The issue arises when trying to filter the valid IDs based on their latest timestamp and status, which requires a more structured approach.

The measure begins by identifying the selected date using MAX('public dim_date'[date]), ensuring it dynamically captures the selected date context. Next, we filter the fact_document_history table to include only rows with dates on or before the selected date. This filtered table serves as the base for further calculations.

To identify the latest timestamp per unique conformed_data_id, we use SUMMARIZE combined with ADDCOLUMNS, grouping the data by conformed_data_id and calculating the maximum event_timestamp within the filtered context. Once we have the latest timestamp for each ID, we filter further to include only rows where the document_status_id equals 2 for the corresponding latest timestamp. Finally, the measure returns the count of IDs meeting all conditions using COUNTROWS.

Here is the DAX code implementing the solution:

Test_ValidIDs =
VAR SelectedDate =
    MAX('public dim_date'[date])

VAR FilteredFactHistory =
    FILTER(
        ALL('public fact_document_history'),
        'public fact_document_history'[date_id] <= SelectedDate
    )

VAR LatestTimestampPerID =
    ADDCOLUMNS(
        SUMMARIZE(
            FilteredFactHistory,
            'public fact_document_history'[conformed_data_id]
        ),
        "LatestTimestamp",
        CALCULATE(
            MAX('public fact_document_history'[event_timestamp])
        )
    )

VAR ValidIDs =
    FILTER(
        LatestTimestampPerID,
        CALCULATE(
            MAX('public fact_document_history'[document_status_id]) = 2,
            FILTER(
                'public fact_document_history',
                'public fact_document_history'[conformed_data_id] = EARLIER('public fact_document_history'[conformed_data_id])
                    && 'public fact_document_history'[event_timestamp] = [LatestTimestamp]
            )
        )
    )

RETURN
    COUNTROWS(ValidIDs)

This measure ensures that for each unique conformed_data_id, the latest timestamp on or before the selected date is evaluated, and only IDs with a document_status_id of 2 are counted. By structuring the logic this way, it satisfies all the specified requirements while maintaining clarity and correctness.

Best regards,

[svniekerk]

Hi DataNinja

If I look at your query the filtered factory history part looks at the public fact document history table. Should it not be on the public dim date table as the page filter is derived from this table.

Kind Regards

Stephan

[svniekerk]

Hi @DataNinja777

Thank you for yor response. Appreciate it. However when I try to test your result in a matrix its not returning any data. From your explanation I can see you understand exactly what I require. I see in your query you use:

VAR FilteredFactHistory =
    FILTER(
        ALL('public fact_document_history'),
        'public fact_document_history'[date_id] <= SelectedDate
    )

Should it not be on the public dim_date table as that is where what the page filter uses to determine the chosen date.

Kind Regards

Stephan