Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
)
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
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.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |