Please help me on ctreating a dax

[Anonymous]

Hi,

I need to implement the below logic:

If (LW_RECEIVE_END_DATE_TIME is not NULL) and (LW_RECEIVE_START_DATE_TIME is not NULL) and (MESSAGE_ERROR_STEP not Like "%RECEIV%" or "%SPLIT%" or "%MULTI%" ) then AVG (LW_RECEIVE_END_DATE_TIME - LW_RECEIVE_START_DATE_TIME)

and I have created the below 3 columns for this:

Recv Split Diff = DATEDIFF('Data'[LW_RECEIVE_START_DATE_TIME],'Data'[LW_RECEIVE_END_DATE_TIME], SECOND)
Recv Split Avg = Calculate(average('Data'[Recv Split Diff]),
Filter(Data,
'Data'[LW_RECEIVE_END_DATE_TIME] <> blank() && 'Data'[LW_RECEIVE_START_DATE_TIME] <> blank() && (
SEARCH("*RECEIV*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*SPLIT*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*MULTI*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1
)
)
)
Recv Split Format =
var DAXDay = INT(Data[Recv Split Avg]/(24*60*60))
var DAXHours = MOD(INT(Data[Recv Split Avg]/(60*60)),24)
var DAXMin = MOD(INT(Data[Recv Split Avg]/60),60)
var DAXSec = MOD(Data[Recv Split Avg],60)
return
FORMAT(DAXHours,"#00")&":"&FORMAT(DAXMin,"#00")&":"&FORMAT(DAXSec,"#00")

But I think the below filter condition is not working for me:

Filter(Data,
'Data'[LW_RECEIVE_END_DATE_TIME] <> blank() && 'Data'[LW_RECEIVE_START_DATE_TIME] <> blank() && (
SEARCH("*RECEIV*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*SPLIT*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*MULTI*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1
)
)

Please help me on this.

[Anonymous]

Hi @Anonymous -

I think you need a "Not" before this:

NOT (
SEARCH("*RECEIV*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*SPLIT*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1 || search("*MULTI*", 'Data'[MESSAGE_ERROR_STEP],1,0)=1
)

[Anonymous]

Hi,

I am getting average time values for message error step where it has value :'SPLIT', so i think my condition is not working:

My condition is:

If (LW_RECEIVE_END_DATE_TIME is not NULL) and (LW_RECEIVE_START_DATE_TIME is not NULL) and (MESSAGE_ERROR_STEP not Like "%RECEIV%" or "%SPLIT%" or "%MULTI%" ) then I need to calculate the average of time value.

Even when LW_RECEIVE_END_DATE_TIME is null, i ma getting average time value, so can i correct the filter condition here?

Even if

[Anonymous]

Hi,
Can anyone help me for this?

[Anonymous]

@Anonymous 

Did you try with the NOT?

Also, I would test the conditions to make sure they're actually blank, with separate calculated columns. e.g.:

Test End Blank = ISBLANK(LW_RECEIVE_END_DATE_TIME)

I also may not be understanding the requirement exactly. If you could post your pbix or a spreadsheet with your desired results, that could with testing.