Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi @all,
I don't have so much experience in creating DAX formula and need your support for this.
I have a table as follows (shortened and simplified version):
Serial number | Date Time | Error Message | Status |
1001 | 28.12.2022 11:28:03 | Error 1 | Failed |
1002 | 28.12.2022 11:33:12 | Error 4 | Failed |
1001 | 28.12.2022 11:44:33 | Error 1 | Failed |
1003 | 28.12.2022 12:02:07 | Error 5 | Failed |
1004 | 28.12.2022 11:28:03 | Passed | |
1002 | 28.12.2022 12:36:11 | Error 18 | Passed |
1001 | 28.12.2022 12:55:52 | Passed | |
1003 | 28.12.2022 12:09:09 | Passed |
Here I want to add a column "First Error" by a DAX formula to get the following result:
Serial number | Date Time | Error Message | Status | First Error |
1001 | 28.12.2022 11:28:03 | Error 1 | Failed | 1 |
1002 | 28.12.2022 11:33:12 | Error 4 | Failed | 1 |
1001 | 28.12.2022 11:44:33 | Error 1 | Failed | 0 |
1003 | 28.12.2022 12:02:07 | Error 5 | Failed | 1 |
1004 | 28.12.2022 11:28:03 | Passed | 0 | |
1002 | 28.12.2022 12:36:11 | Error 18 | Passed | 0 |
1001 | 28.12.2022 12:55:52 | Passed | 0 | |
1003 | 28.12.2022 12:09:09 | Passed | 0 |
Requirement: I want the "First Error" column to contain a 1 when an error has occurred for a serial number for the first time. So the time should be evaluated by the column "Date Time". If there are several errors for a serial number (also the same error), there should be 0 in the column.
I would be happy to receive suggestions 🙂
Best regards
Nemo999
Solved! Go to Solution.
-
Thanks for your approach. But I still have the remaining issue I already mentioned below.
@Anonymous ,
a new column
=
var _min = minx(filter(Table, [Serial Number] = earlier( [Serial Number]) ),[Date time])
return
if( [Date Time] =_min , 1,0)
Thank for your rapid feedback!
I tried your approach with real data and figured out that most of my requirements are covered with only one exception:
In this case...
1004 | 28.12.2022 11:28:03 | Passed | 0 |
no error occures and the value for "First Error" shall be 0. Currently the formula returns 1.
It seems so that the pararmeter "Status" must also be considered. Do you agree? Any suggestions?
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |