Nulls in Power BI in Direct Query mode

[Anonymous]

Hi All,

I observed that Power BI included the count of Nulls when calculating the averages. What's more surprising is that, Power BI considers Nulls as 0 which is not correct first of all and this assumption by Power Bi resulted in erroneous average calculation results for us. 

Unfortunately, there is no isnull function available, we had to use apply the filter to ignore the 0s in the data (even though we didn't have any 0s) to take care of the Nulls. Since we use direct query mode, we can't change anything at the query level to manually replace nulls with other values or blanks.

Is there a better solution to this problem? Is Power BI coming up with something to address this?

Thank you,

Vidya

[Levajar]

Did you try below link ? 

https://docs.microsoft.com/en-us/dax/isblank-function-dax 

It has an example for one scenario : 

=IF( SUM(InternetSales_USD[SalesAmount_USD])= 0 , BLANK() , SUM(ResellerSales_USD[SalesAmount_USD])/SUM(InternetSales_USD[SalesAmount_USD]) )

[Anonymous]

Hi @Levajar 

We do not have any blanks in our data, only few nulls and since I mentioned before Power Bi was considering these nulls as zeroes we had incrrect averages. 

Thanks

[v-piga-msft]

Hi @Anonymous ,

By my tests, we could use ISBLANK function in direct query mode.

We could use ISBLANK function to filter the value is null and then calculate the average.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

Hello @v-piga-msft ,

When I tried to use the ISBLANK(), the result is empty. That's why we had to apply the filter to ignore the 0s in the data. In our case, it seems like Power BI is considering nulls as zeros which is not right. I will try to prepare the test data smaple for you if needed.

Thanks,