Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Hi @Anonymous
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.
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)
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?
There is something wrong is the first line. First doubt is all on table and second is column name taken correctly in earlier
It errors out as soon as I enter return...what could the issue be?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |