Forum Discussion
Comparing a duration measures with Durations
Hi,
Please I have a table with the duration in text colum "0:00:00", I converted it into duration using
= Table.AddColumn(#"Inserted Second", "Merged", each Text.Combine({Text.From([Hour], "en-US"), ":", Text.From([Minute], "en-US"), ":", Text.From([Second], "en-US")}), type text)
and it come out fine in the edit query, but it shows as decimal in the viaual section as below
I was able to convert it and calculate sum and averages
durationaverages = time(hour(AVERAGE(CallsHolsActivity[CallTimeDuration])),minute(average(CallsHolsActivity[CallTimeDuration])),second(average(CallsHolsActivity[CallTimeDuration])))
durationsum = time(hour(SUM(CallsHolsActivity[CallTimeDuration])),minute(SUM(CallsHolsActivity[CallTimeDuration])),second(SUM(CallsHolsActivity[CallTimeDuration])))
but when I try to compare the sum or averages to a time using the formular below, the filter and summarisation on the table stops working and it shows the data that are filtered out without values
TargetTest = IF([durationaverages] < time(1,30,0),1,0)
Please is there a logick I can use to compare the measure (average) without it affecting the summarisation of the data. Thank you
If I understand you correctly, you want the column "TargetTest" to be shown, but only for non-blank lines, is that correct? In that case, wrap your formula into another IF:
IF (ISBLANK ( durationaverages ) = FALSE(),
IF([durationaverages] < time(1,30,0),1,0)
)This will return a BLANK value for blank rows, so these rows won't be displayed anymore. They are shown in your situation because BLANK might be evaluated as 0 in some cases. You might want to have a look at this article: https://www.sqlbi.com/articles/blank-handling-in-dax/
2 Replies
If I understand you correctly, you want the column "TargetTest" to be shown, but only for non-blank lines, is that correct? In that case, wrap your formula into another IF:
IF (ISBLANK ( durationaverages ) = FALSE(),
IF([durationaverages] < time(1,30,0),1,0)
)This will return a BLANK value for blank rows, so these rows won't be displayed anymore. They are shown in your situation because BLANK might be evaluated as 0 in some cases. You might want to have a look at this article: https://www.sqlbi.com/articles/blank-handling-in-dax/