Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Status Flagging.png

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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
    )

Capture1.JPG

 

 

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

Capture2.JPG

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.

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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
Community Support
Community Support

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
    )

Capture1.JPG

 

 

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

Capture2.JPG

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
Super User
Super User

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)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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?

 

 

Status Flagging_Response.png

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

Anonymous
Not applicable

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

Status Flagging_Response2.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.