AVERAGE function not working and returning 0

[Anonymous]

Hello,

I'm using this very basic measure: 

Measure = AVERAGE('platform inbound_case'[Case Duration])

But when I put the result in a Card I get: 0,00

The case duration column looks like this:

I don't understand why it's returning 0...

Thanks a lot for your help

Regards

[amitchandak]

@Anonymous , Sound strange. Please check there is no page level, or visual level filter.

data type is decimal or whole number.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hello,

Thank you for your reply.

I double checked and there's no filters applied.

Data type is decimals.

I'm using DirectQuery so I can only display data in visuals as I've done with the screenshot. It's mainly populated with high numbers so I don't really understand what can explain this zero result.

But on the Micorsoft documentation I found that: 

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

There's no RLS but I'm not sure about the first point, what does it means exactly?

[Anonymous]

HI @Anonymous,

What type of value are you calculated on? AFAIK, current power b cannot directly use aggregation function on date or time values. I'd like to suggest you convert them to numeric values first and use the aggregate function to calculate. 

For aggregate duration values, you can take a look at the following blog:

Aggregating Duration Time 

>>This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

In direct query mode, power bi has limited DAX function usage when you are trying to create calculated column/table expressions. Please use measure expression instead.

BTW, please also check the decimal place to confirm if the result value is less than 1 and show with 0 decimal places.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello, thank you for your answer 🙂

The data type is already decimals not date or time.

I checked the decimals and there's no hidden numbers.

Thank you