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/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

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.

6 Replies

  • 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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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