Forum Discussion

burnttoast's avatar
burnttoast
New Member
1 year ago
Solved

Look for an expected blank/missing entry

I have the following data sample and I'm looking to show the below.

Every business day the doors are UNLOCKED in the morning and locked in the evening.

I need to show the current status of each door for each day, or mainly the latest day.

So if it's locked I don't need the unlocked and if it's unlocked I need to show the unlocked until it is changed.

 

Source

 

Expected

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi burnttoast  - you can create a measure to dynamically retrieve the most recent status as like below:

     

     

    Latest Status =
    VAR LatestDate = MAX('Lockd'[LatestDate])
    RETURN
    CALCULATE(
        MAX('Lockd'[status]),
        'Lockd'[LatestDate] = LatestDate
    )
     
     

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi burnttoast  - you can create a measure to dynamically retrieve the most recent status as like below:

     

     

    Latest Status =
    VAR LatestDate = MAX('Lockd'[LatestDate])
    RETURN
    CALCULATE(
        MAX('Lockd'[status]),
        'Lockd'[LatestDate] = LatestDate
    )
     
     

     

     

    • burnttoast's avatar
      burnttoast
      New Member
      1 year ago

      Thanks rajendraongole1,

       

      Thant's done the trick.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ,

    The method rajendraongole1 provided should be helpful.

    Besides, you can also try the following DAX formula to display the door status.

    DisplayStatus = 
VAR LatestDate = 
    CALCULATE(
        MAX('Table'[LatestDate]),
        ALLEXCEPT('Table', 'Table'[device_location])
    )
var _result = CALCULATE(
    MIN('Table'[status]),
    'Table'[LatestDate] = LatestDate,
    ALLEXCEPT('Table', 'Table'[device_location])
)
RETURN
_result

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.