Column Calculation Exclude Blank Cells

[JohnnyT48]

I have measure working for a KPI = CALCULATE(AVERAGE('Table'[Section]),FILTER('Table','Table[Section]<>0))/5.16

This works as a DAX Measure. So as a measure this gives a return and works and excludes the blanks.

If i put this into a column calculated it shows this error "a circular depdency was detected"

I need to create the same caulcaiton in a column and cannot get it to work.

I can do an average in the column calcualtion but it counts blanks also., cannot get it to exclude blanks:

1. Can someone explain how to use the measure calculation in a column measure,

or 

2. How to make how to exclude blanks from a column calculation

PLease note i am working with a direct query live feed

Thanks

[Anonymous]

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.

[JohnnyT48]

Tried the cauclation same as you for the column and got the following message:

A circualr dependcy was detected.

This only happens when I apply the filter, if caculate avergae only without filter it works ok.

Thanks

Johnny

[Anonymous · Accepted solution]

@JohnnyT48 
Thank you for your replay .

If you can't change the connection mode of your data from DirectQuery to Import mode, it's not a wise choice to create a calculate column, because after my test, most of the dax functions don't work in Direct Query connection mode so please check if your connection mode is Direct Query
After my test, I was not able to use most of the functions in calculate column in this mode (Power BI doesn't prompt for the information)

like this:( I can't even use the calculate function in calculate column with Direct Query mode )

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.

[JohnnyT48]

Thanks for your detailed response. 

Unfortunately I am not in control of changing to Import Mode or using Composite Models etc. 

I have tried some of the features, using a simple AVERAGE with a filter gives the error of circular model.

Starting to think there is no way to do this within Direct Query, will need to push back to IT who control these features. 

Appreciate you taking the time.