DAX Measure for Audit Log

[toinktoink]

I have a table thats almos tan audit log table. An order only gets a record for a particular date if the status of the order changes. I want to summarize the number of orders for each status by date. I know how to do this through a calculated table, but trying to do it as a measure:

For example, Order '234' does not show up for 2/1/2018 because it's status was not changed. But in my measure, when I summaruze orders by status for 2/1/2018, I want this order to show up under the 'Open' status because that was the latest status for this order.

Looking for ideas!

[AlbertoFerrari]

Hi,

please share a sample data model. The code is not hard to author, but it will be slow... that said, unless you have tens of millions of rows this should not be an issue.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[toinktoink]

Hi I have a table, almost an audit log, where only those orders are repeated where the status is changed. I want to have a measure that can tell me the count of orders by status for any date. I know how to do it in a separate calculated table but trying to see if I can do it as a measure:

Please note that Order '234' does not show up for 2/1/2018 because it's status has not changed. But when I do a sum of all status as of 2/1/2018, I want this order to be included in the 'Open' count since that was the latest status.

[Zubair_Muhammad]

@toinktoink

Try this MEASURE.

Measure =
VAR temp1 =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] <= SELECTEDVALUE ( Table1[Date] ) ),
        Table1[OrderID],
        "Last Status", CALCULATE ( VALUES ( Table1[Status] ), LASTDATE ( Table1[Date] ) )
    )
VAR mystatus =
    SELECTEDVALUE ( Table1[Status] )
RETURN
    COUNTX ( FILTER ( temp1, [Last Status] = mystatus ), [OrderID] )

[Zubair_Muhammad · Accepted solution]

@toinktoink

It seemingly works with your sample data.

Please see the attached file as well.