Compare hours within a measure

[Spotto]

Hi guys, I need some help.
I have a measure that takes the hours of a date/time and counts how many IDs had the time greater than 11hs, but this is giving an error.
thank you for the help

Time =

var horas = TIMEVALUE(MAX('gdp tb_Pagamento_Contrato'[Data_Liberado_PGTO]))

var solucao =

CALCULATE(

COUNT('gdp tb_Pagamento_Contrato'[ID_Contrato]),

horas > time(11,00,00)

)

return solucao

[Anonymous · Accepted solution]

Hi @Spotto ,

Please try

Time =
CALCULATE (
    COUNT ( 'gdp tb_pagamento_Contrato'[ID_Contrato] ),
    FILTER (
        'gdp tb_pagamento_Contrato',
        TIMEVALUE ( 'gdp tb_pagamento_Contrato'[Data_Liberado_PGTO] )
            > TIME ( 11, 0, 0 )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Spotto 
in this DAX I can see that in the variable "horas" you found the max value and converted it to a time value. Which gives you a single scalar value that is the maximum value in that column. Then you are comparing it with time(11,00,00) which is also a scalar value.
So you are comparing two fixed scalar values in the filter expression and are not really including any column in the table filter expression in the CALCULATE() function. This is exactly what the error statement is saying
In order to fix this error please include a column from the table in the filter expression.

[Whitewater100]

Hi:

Not sure if this will help.

Assuming you have a srat and finish time, you can caclulate out the difference in hours. If the hours are 11 or more you can add a 1 to column. See below:

Calc Column Measure.

Meet 11 Hrs = IF(Sheet1[End] > Sheet1[11HourDiff],1,BLANK())

Then you can sum this column up.

How it looks with data: My table name is Sheet 1

[Spotto]

thanks for your answer but I'm making an application with directquery and I can't (as far as I know) change the table structure so I'm doing it by measures.