Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Current Status Filter

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/Ite...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      amitchandak
      Super User
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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