Current Status Filter

[Anonymous]

I have a Power BI table with a Location/ Item key, Audit Date and Item Status (as shown below).  I need to add a filter in my reports that will shows only the most current status of each Location/Item key.   Foe example, I need the filter to only give the results of the items highlighted in blue below (because they show the most current status) which is also illustrated in the second screen shot below that shows only the most current status for the items.  How can I set this up in Power BI?

Thx

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure and add it to visual level filter, then add [Status] to a slicer, you can filter the status.

flag1 =
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Location/Item]
                = MAX ( 'Table'[Location/Item] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Date] ) = maxdate,
        1,
        0
    )

2. if you want to apply filter (active or inactive) in this manner:

show the lastest date's data which their status is active or inactive via slicer.

You could create a table

Status = VALUES('Table'[Status])

Add [status] from this table to slicer, create measure below and add to viusal level filter

flag2 =
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Location/Item]
                = MAX ( 'Table'[Location/Item] )
        )
    )
VAR maxdate_m =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Location/Item]
                = MAX ( 'Table'[Location/Item] )
                && 'Table'[Status]
                    = SELECTEDVALUE ( 'Status'[Status] )
        )
    )
VAR switch1 =
    IF (
        HASONEVALUE ( 'Status'[Status] ),
        maxdate_m,
        maxdate
    )
RETURN
    IF (
        MAX ( 'Table'[Date] ) = switch1,
        1,
        0
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

So try creating a column like this:

Most Current Column = 
  VAR __Latest = MAXX(FILTER(ALL('Table'), [Location/Item] = EARLIER([Location/Item]),[Date])
RETURN
  IF([Date] = __Latest, TRUE, FALSE)

[Anonymous]

Thx Greg,

Here is my formula along with the error I got:

Store Item is the Location/Item in my inital example and New_Audit Date is the Date in my example.  Thoughts?  Also if I want to filter by the Status of "Active" and "Inactive" how does that come into play in this scenario?

[amitchandak]

There is something wrong is the first line. First doubt is all on table and second is column name taken correctly in earlier

[Anonymous]

It errors out as soon as I enter return...what could the issue be?