Forum Discussion

Frequent Visitor

4 years ago

Solved

Filtering data via DAX

Hi all, I have a table and I am trying to identify devices based on the below conditions Failed Device - [OutputVolt < ReferenceVolt] in 'VOLT_MEAS', 'CURR_MEAS', 'FUNC_TEST', 'NON_FUNC' pro...

Need Help

ryan_mayu

4 years ago

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

4 years ago

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

Super User

4 years ago

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

Frequent Visitor

4 years ago

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

Super User

4 years ago

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

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

Frequent Visitor

4 years ago

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
Super User
4 years ago
you are welcome

Forum Discussion

Filtering data via DAX

Related content

Filtering Data

Filter data using slicer

Bookmarks Filter Data

Data Filter Scenario

Filtering Data challenge