Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Looking to get a list of only the Model where Status went from Active to {Inactive, Unknown, anything else} and back to Active.
The status change can happen multiple times in a day.
Following scenario should return only Model 1 as the status went from Active to some other status and came back to Active.
Here's the sample data,
Appreciate your inputs!
First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Second, I'm not sure I am 100% on the requirements. If things change multiple times during a single day, there is no time component so it would be impossible to tell the "last" or "first" value unless you have something like an Index column.
Third, perhaps something like the following:
Measure =
VAR __Model = MAX('Table'[Model])
VAR __FirstDate = MINX('Table',[Date])
VAR __LastDate = MAXX('Table',[Date])
VAR __FirstStatus = MINX(FILTER('Table','Table'[Date]=__FirstDate),[Status])
VAR __LastStatus = MAXX(FILTER('Table','Table'[Date]=__LastDate),[Status])
VAR __OtherStatus =
IF(
COUNTROWS(
FILTER(
ALL('Table'),
'Table'[Date] >= [FirstDate] &&
'Table'[Date] <= [LastDate]) &&
)
) > 0,
1,0
)
RETURN
IF(__FirstStatus = "Active" && __LastStatus = "Active" && __OtherStatus = 0,1,0)
Put that in a visualization along with Model.
Hi @Greg Thank you for your inputs. I will keep the guidelines in mind.
Let's ignore the same day changes for the sake of simplicity.
While looking at the assignment for __OtherStatus, did you mean to add another filter for Status column? The way I understand, it is counting all rows rows between FirstDate and LastDate and assigning 1 or 0 based on that. Shouldn't it count the rows when 'Table'[Status] IN {"Unknown", "Inactive", "Something else"}
Here's the actual table instead of a picture.
Date | Model | Status |
20200301 | 1 | Active |
20200301 | 2 | Active |
20200303 | 2 | Active |
20200304 | 1 | Inactive |
20200304 | 2 | Active |
20200305 | 1 | Unknown |
20200305 | 4 | Active |
20200305 | 3 | Unknown |
20200307 | 1 | Active |
20200308 | 3 | Unknown |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |