Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a table and I am trying to identify devices based on the below conditions
DeviceSerial | TestDate | ProcessName | TestStatus | OutputVolt | ReferenceVolt | [Output < Reference]? |
ABC123 | 16-02-22 11:35:32.697 PM | VOLT_MEAS | Fail | 1.27 | 2.00 | Yes |
ABC123 | 16-02-22 11:49:43.570 PM | VOLT_MEAS | Pass | 2.26 | 2.00 | Yes |
ABC123 | 17-02-22 1:15:14.507 AM | CURR_MEAS | Pass | 3.21 | 2.00 | Yes |
ABC123 | 17-02-22 1:15:37.013 AM | FUNC_TEST | Fail | 1.56 | 2.00 | No |
ABC123 | 17-02-22 1:17:00.343 AM | FUNC_TEST | Pass | 3.45 | 2.00 | Yes |
ABC123 | 17-02-22 1:20:29.997 AM | NON_FUNC | Pass | 2.58 | 2.00 | No |
ABC123 | 17-02-22 1:55:55.300 AM | CONFIG_TEST | Fail | 1.28 | 2.00 | Yes |
ABC123 | 17-02-22 2:49:44.000 AM | CONFIG_TEST | Pass | 3.24 | 2.00 | Yes |
ABC124 | 18-02-22 2:49:43.995 AM | VOLT_MEAS | Pass | 2.26 | 2.00 | Yes |
ABC124 | 19-02-22 2:49:43.995 AM | CURR_MEAS | Pass | 3.45 | 2.00 | Yes |
ABC124 | 20-02-22 2:49:43.995 AM | FUNC_TEST | Pass | 2.58 | 2.00 | Yes |
ABC124 | 21-02-22 2:49:43.995 AM | NON_FUNC | Pass | 3.24 | 2.00 | Yes |
BBS319 | 22-02-22 2:50:44.000 AM | VOLT_MEAS | Pass | 2.26 | 2.00 | Yes |
BBS319 | 23-02-22 2:50:44.000 AM | CURR_MEAS | Pass | 3.21 | 2.00 | Yes |
BBS319 | 25-02-22 2:50:44.000 AM | FUNC_TEST | Pass | 3.45 | 2.00 | Yes |
BBS319 | 26-02-22 2:50:44.000 AM | NON_FUNC | Pass | 2.58 | 2.00 | No |
BBS319 | 27-02-22 2:50:44.000 AM | CONFIG_TEST | Pass | 2.58 | 2.00 | Yes |
Expected Output:
DeviceSerial | ProcessName | Status |
ABC123 | CONFIG_TEST | Failed Device |
ABC124 | CONFIG_TEST | Did not Pass/NoRecord |
BBS319 | CONFIG_TEST | No issue |
Tried with the following DAX but I couldn't get to the bottom of it. I have also uploaded the power BI file here (Dropbox link)
DeviceSNo =
CALCULATE (
VALUES ( 'Table'[DeviceSerial] ),
FILTER (
'Table',
[OutputVolt] < [ReferenceVolt]
&& ([ProcessName] IN { "VOLT_MEAS", "CURR_MEAS", "FUNC_TEST", "NON_FUNC"})
),
FILTER(
'Table',
[ProcessName] = "CONFIG_TEST"
)
)
Could you please guide me how to achieve this via DAX ?
Thanks in advance!
Solved! Go to Solution.
you can create a column
Column =
VAR _volt=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="VOLT_MEAS"),'Table'[[Output < Reference]]?])
VAR _curr=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="CURR_MEAS"),'Table'[[Output < Reference]]?])
VAR _func=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="FUNC_TEST"),'Table'[[Output < Reference]]?])
VAR _non=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="NON_FUNC"),'Table'[[Output < Reference]]?])
VAR _config=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="CONFIG_TEST"),'Table'[TestStatus])
return if(_volt="Yes"&&_curr="Yes"&&_func="Yes"&&_non="Yes"&&_config="Pass","Failed Device",if(_volt="Yes"&&_curr="Yes"&&_func="Yes"&&_non="Yes"&&(_config="Fail"||_config=""),"Did not pass/no record",if(_volt="No"&&_curr="No"&&_func="No"&&_non="No"&&_config="Pass","no issue")))
what do you mean [OutputVolt < ReferenceVolt] in xxx? should match for all the process or any one process?
Proud to be a Super User!
@ryan_mayu: I have updated the DAX according to the logic and managed to get it working. Thank you very much for your inputs 🙂
Column =
VAR _volt =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "VOLT_MEAS"
),
'Table'[[Output < Reference]]?]
)
VAR _curr =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "CURR_MEAS"
),
'Table'[[Output < Reference]]?]
)
VAR _func =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "FUNC_TEST"
),
'Table'[[Output < Reference]]?]
)
VAR _non =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "NON_FUNC"
),
'Table'[[Output < Reference]]?]
)
VAR _config =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "CONFIG_TEST"
),
'Table'[TestStatus]
)
RETURN
IF (
_volt = "No"
&& _curr = "No"
&& _func = "No"
&& _non = "No"
&& _config = "Pass",
"no issue",
IF (
( _volt = "Yes"
|| _curr = "Yes"
|| _func = "Yes"
|| _non = "Yes" )
&& _config = "Pass",
"Failed Device",
IF (
( _volt = "No"
|| _curr = "No"
|| _func = "No"
|| _non = "No" )
&& ( _config = "Fail"
|| _config = "" ),
"Did not pass/no record",
"BLANK"
)
)
)
you can create a column
Column =
VAR _volt=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="VOLT_MEAS"),'Table'[[Output < Reference]]?])
VAR _curr=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="CURR_MEAS"),'Table'[[Output < Reference]]?])
VAR _func=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="FUNC_TEST"),'Table'[[Output < Reference]]?])
VAR _non=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="NON_FUNC"),'Table'[[Output < Reference]]?])
VAR _config=maxx(FILTER('Table','Table'[DeviceSerial]=EARLIER('Table'[DeviceSerial])&&'Table'[ProcessName]="CONFIG_TEST"),'Table'[TestStatus])
return if(_volt="Yes"&&_curr="Yes"&&_func="Yes"&&_non="Yes"&&_config="Pass","Failed Device",if(_volt="Yes"&&_curr="Yes"&&_func="Yes"&&_non="Yes"&&(_config="Fail"||_config=""),"Did not pass/no record",if(_volt="No"&&_curr="No"&&_func="No"&&_non="No"&&_config="Pass","no issue")))
what do you mean [OutputVolt < ReferenceVolt] in xxx? should match for all the process or any one process?
Proud to be a Super User!
@ryan_mayu: Thanks for your input. Tried this but it only highlights devices that 'Did not pass/no record'
There's no specific value for [OutputVolt < ReferenceVolt] as it will be dynamic (basically if the 'OutputVolt' column value is less than 'ReferenceVolt' it is considered as a failure) and it should match for all process. I hope this answers your question.
Yes, that's why I ask you the logic
in means AND or OR, that decides how we write the DAX.
FOR ABC 123, the last value of below record is NO, which means OutputVolt > ReferenceVolt. That does not match your logic.
ABC123 | 17-02-22 1:15:37.013 AM | FUNC_TEST | Fail | 1.56 | 2.00 | No |
for BBS319, the last value of below records are Yes, whch means OutputVolt < ReferenceVolt, does not match your logic as well
BBS319 | 22-02-22 2:50:44.000 AM | VOLT_MEAS | Pass | 2.26 | 2.00 | Yes |
BBS319 | 23-02-22 2:50:44.000 AM | CURR_MEAS | Pass | 3.21 | 2.00 | Yes |
BBS319 | 25-02-22 2:50:44.000 AM | FUNC_TEST | Pass | 3.45 | 2.00 | Yes |
That's why we can only get expected result for the second ID.
Proud to be a Super User!
@ryan_mayu: I have updated the DAX according to the logic and managed to get it working. Thank you very much for your inputs 🙂
Column =
VAR _volt =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "VOLT_MEAS"
),
'Table'[[Output < Reference]]?]
)
VAR _curr =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "CURR_MEAS"
),
'Table'[[Output < Reference]]?]
)
VAR _func =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "FUNC_TEST"
),
'Table'[[Output < Reference]]?]
)
VAR _non =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "NON_FUNC"
),
'Table'[[Output < Reference]]?]
)
VAR _config =
MAXX (
FILTER (
'Table',
'Table'[DeviceSerial] = EARLIER ( 'Table'[DeviceSerial] )
&& 'Table'[ProcessName] = "CONFIG_TEST"
),
'Table'[TestStatus]
)
RETURN
IF (
_volt = "No"
&& _curr = "No"
&& _func = "No"
&& _non = "No"
&& _config = "Pass",
"no issue",
IF (
( _volt = "Yes"
|| _curr = "Yes"
|| _func = "Yes"
|| _non = "Yes" )
&& _config = "Pass",
"Failed Device",
IF (
( _volt = "No"
|| _curr = "No"
|| _func = "No"
|| _non = "No" )
&& ( _config = "Fail"
|| _config = "" ),
"Did not pass/no record",
"BLANK"
)
)
)
you are welcome
Proud to be a Super User!
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |