Conditional Filtering of table in Power BI

Question

I have certain requirement which I am tryin to implement with help of Power BI. I have data which looks something like as shown below:

Equipment   Date         Val
AV001   2/6/19 12:10 AM  24
AV001   2/6/19 12:12 AM  22
AV001   2/6/19 12:20 AM  32
AV001   4/6/19 12:24 AM  28
AV001   4/6/19 12:25 AM  25
AV001   4/6/19 12:28 AM  27
AV002   5/6/19 12:00 AM  25
AV002   5/6/19 12:10 AM  24
AV002   6/6/19 12:12 AM  23
AV003   7/6/19 12:03 AM  29
AV003   7/6/19 12:05 AM  26
AV003   7/6/19 12:06 AM  24
AV003   7/6/19 12:09 AM  22

I want to create a table out of this which will be having Equipment, Date & Time with its value. Like shown below:

AV001   2/6/19 12:20 AM  32
AV001   4/6/19 12:28 AM  27
AV002   5/6/19 12:10 AM  24
AV002   6/6/19 12:12 AM  23
AV003   7/6/19 12:09 AM  22

The desired table should have Equipment , date with latest time and its corresponding Val.

Kindly let me know any possible way to get the desired table.

It would be helpful if you could edit your post to use text instead of images for your data tables. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-03T17:48:32

The hard part here is that you need to group on date but there isn't a column for that. Here's one possibility though where I use INT to truncate the datetime column to just a date.

FILTER (
    Table1,
    Table1[Date]
        = CALCULATE (
            MAX ( Table1[Date] ),
            FILTER (
                Table1,
                Table1[Equipment] = EARLIER ( Table1[Equipment] ) &&
                INT ( Table1[Date] ) = INT ( EARLIER ( Table1[Date] ) )
            )
        )
)

If you added INT ( Table1[Date] ) as a calculated column to Table1, then it's a bit simpler:

FILTER (
    Table1,
    Table1[Date]
        = CALCULATE (
            MAX ( Table1[Date] ),
            ALLEXCEPT ( Table1, Table1[Equipment], Table1[DateInt] )
        )
)

Conditional Filtering of table in Power BI

1 Answers