Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello datanauts,
In PBI (example in the table below), I need to get the column "VeryFirstTestOkay", which gives out boolean values according to multiple filtering conditions.
The column "VeryFirstTestOkay" shall only return 1 if:
In humane words: I have to find out, if an article has gone through a test without errors at the first time or not.
Article | Timestamp | Text | VeryFirstTestOkay | Why "FirstTestOkay" has it's value |
A | 04.08.2020 10:27 | System Start | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
A | 04.08.2020 10:29 | Resistance Test | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
A | 04.08.2020 10:30 | 0 Errors | 1 | 1 - because "Text" does contain ">0 Errors" |
B | 04.08.2020 10:35 | System Start | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
B | 04.08.2020 10:36 | Failure | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
C | 04.08.2020 10:40 | System Start | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
C | 04.08.2020 10:41 | Resistance Test | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
C | 04.08.2020 10:43 | 1 Error | 0 | 0 - because "Text" does contain ">0 Errors" |
C | 04.08.2020 10:44 | System Start | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
C | 04.08.2020 10:46 | 0 Errors | 1 | 1 - because "Text" does contain ">0 Errors" and article hasn't been tested before. |
A | 05.08.2020 15:11 | System Start | 0 | 0 - because "Text" doesn't contain ">0 Errors" |
A | 05.08.2020 15:12 | 1 Error | 0 | 0 - because "Text" doesn't contain ">0 Errors" & "VeryFirstTestOkay" has already been set to 1 for this article |
Solved! Go to Solution.
@Anonymous - So basically this is Lookup Min/Max https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
VeryFirstTestOkay =
VAR __Min = MINX(FILTER('Table',[Text] = "0 Errors" && [Article] = EARLIER('Table'[Article])),[Timestamp]
RETURN
IF([Timestamp] = __Min && [Text] = "0 Errors",1,0)
@Anonymous - So basically this is Lookup Min/Max https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
VeryFirstTestOkay =
VAR __Min = MINX(FILTER('Table',[Text] = "0 Errors" && [Article] = EARLIER('Table'[Article])),[Timestamp]
RETURN
IF([Timestamp] = __Min && [Text] = "0 Errors",1,0)
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |