Filtering data via DAX

[DreamToGet]

Hi all,

I have a table and I am trying to identify devices based on the below conditions

1. Failed Device - [OutputVolt < ReferenceVolt] in 'VOLT_MEAS', 'CURR_MEAS', 'FUNC_TEST', 'NON_FUNC' process and passed 'CONFIG_TEST'
2. Did not Pass/NoRecord - [OutputVolt < ReferenceVolt] in 'VOLT_MEAS', 'CURR_MEAS', 'FUNC_TEST', 'NON_FUNC' process and did not pass or no records in  'CONFIG_TEST'
3. No issue - [OutputVolt > ReferenceVolt] in 'VOLT_MEAS', 'CURR_MEAS', 'FUNC_TEST', 'NON_FUNC' process and passed 'CONFIG_TEST'

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!

[ryan_mayu · Accepted solution]

@DreamToGet 

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?

[ryan_mayu · Accepted solution]

@DreamToGet 

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?

[DreamToGet]

@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.

[ryan_mayu]

@DreamToGet 

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.

[DreamToGet · Accepted solution]

@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"
            )
        )
    )

[ryan_mayu]

you are welcome