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' process and passed 'CONFIG_TEST'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'No issue - [OutputVolt > ReferenceVolt] in 'VOLT_MEAS', 'CURR_MEAS', 'FUNC_TEST', 'NON_FUNC' process and passed 'CONFIG_TEST' DeviceSerialTestDateProcessNameTestStatusOutputVoltReferenceVolt[Output < Reference]?ABC12316-02-22 11:35:32.697 PMVOLT_MEASFail1.272.00YesABC12316-02-22 11:49:43.570 PMVOLT_MEASPass2.262.00YesABC12317-02-22 1:15:14.507 AMCURR_MEASPass3.212.00YesABC12317-02-22 1:15:37.013 AMFUNC_TESTFail1.562.00NoABC12317-02-22 1:17:00.343 AMFUNC_TESTPass3.452.00YesABC12317-02-22 1:20:29.997 AMNON_FUNCPass2.582.00NoABC12317-02-22 1:55:55.300 AMCONFIG_TESTFail1.282.00YesABC12317-02-22 2:49:44.000 AMCONFIG_TESTPass3.242.00YesABC12418-02-22 2:49:43.995 AMVOLT_MEASPass2.262.00YesABC12419-02-22 2:49:43.995 AMCURR_MEASPass3.452.00YesABC12420-02-22 2:49:43.995 AMFUNC_TESTPass2.582.00YesABC12421-02-22 2:49:43.995 AMNON_FUNCPass3.242.00YesBBS31922-02-22 2:50:44.000 AMVOLT_MEASPass2.262.00YesBBS31923-02-22 2:50:44.000 AMCURR_MEASPass3.212.00YesBBS31925-02-22 2:50:44.000 AMFUNC_TESTPass3.452.00YesBBS31926-02-22 2:50:44.000 AMNON_FUNCPass2.582.00NoBBS31927-02-22 2:50:44.000 AMCONFIG_TESTPass2.582.00Yes Expected Output: DeviceSerialProcessNameStatusABC123CONFIG_TESTFailed DeviceABC124CONFIG_TESTDid not Pass/NoRecordBBS319CONFIG_TESTNo 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!

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

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' process and passed 'CONFIG_TEST'
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'
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!

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

5 Replies

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

Forum Discussion

Filtering data via DAX

5 Replies

Related content

Filtering Data

Filter data using slicer

Bookmarks Filter Data

Data Filter Scenario

Filtering Data challenge