Column Calculation Exclude Blank Cells

Hi,JohnnyT48 
I am glad to help you.

According to your description, you created Measure for Direct Query in connection mode and it worked fine: it calculated the correct result and excluded gaps.

However, when you apply the same syntax to calculate column, it has problems: it doesn't calculate the correct result and reminds about the circular dependency problem.

Let's analyze your problems in turn.

1. Converting a measure to a calculate column

Generally there are problems when copying measure code directly into a new calculate column: the calculate column may report an error, or the result may not be the same as the measure.

This is due to differences in the settings of measure and calculate column.

The result of the measure calculation depends on the current filtering context: the current filtering conditions, and the filtering caused by the user's actions (using slicers or field filtering) can cause the measure value to change dynamically.

A calculate column is a static value that is added to a table after a field has been calculated, and like a normal column, it is calculated in the row context, i.e. the same calculation is performed for every row.

Calculated columns take up RAM memory even if they are not used in the report, whereas measures do not.

So if you want to achieve the same effect in calculate column as in measure, you need to rewrite the DAX code instead of copying it directly.

2. Use calculate column in Direct Query connection mode will have limitations

Because Direct Query is directly from the data source to query data in real time , there is no way to see the Table of data ; while calculate column is loaded when the data is calculated row by row and added to the table , the two of them together when there will be a lot of problems :

Most of the aggregation function can not be used, even if it can run normally, the code will have an error alert.

Here are my test results:

Direct Query   mode:

The result of calculate column is wrong.

But in import mode, the calculate column no longer has a lot of restrictions, but you can see that the results of C_KPI and M_KPI are different, I modified the code in the calculate column, the results are as shown in “Column”

M_KPI = 
CALCULATE(AVERAGE('HumanResources vEmployee'[BusinessEntityID]),FILTER('HumanResources vEmployee','HumanResources vEmployee'[Title]<>"Ms."))

C_KPI = 
CALCULATE(AVERAGE('HumanResources vEmployee'[BusinessEntityID]),FILTER('HumanResources vEmployee','HumanResources vEmployee'[Title]<>"Ms."))

Column = 
VAR valueID=[BusinessEntityID]
RETURN
CALCULATE(AVERAGE('HumanResources vEmployee'[BusinessEntityID]),FILTER('HumanResources vEmployee','HumanResources vEmployee'[Title]<>"Ms."&&'HumanResources vEmployee'[BusinessEntityID]=valueID))

Therefore, it is recommended that you change Direct Query to Import mode or use the original measure.

It is not recommended to create a calculate column in Direct Query mode.

Here is some documentation that may help you.
DirectQuery in Analysis Services 2016 - SQLBI
DirectQuery in Power BI - Power BI | Microsoft Learn
3. Regarding your question in the code on how to exclude blank in calculate columns

You can mark Null in the calculate column in power BI.

For example, use the following code:

Value_notNull = IF(

    NOT(ISBLANK('Table'[Values])),

    1,0

)

In later calculations you can determine if the current data is null by marking out 1 or 0

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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