Create two "filtered" columns utilizing a measure.

[jstein91694]

I have just created a measure which incorporates data from two different queries. I am now trying to use this measure to create two new columns using a slightly different filter based on existing columns. I am able to create the first of which by doing the following.

SH Input (mmBTU) =

CALCULATE('GAHP-Trane-Canada'[GAHP Gas Input (mmBTU)],

'GAHP-Trane-Canada'[GAHP Gas Use DELTA (cf)]>0,'GAHP-Trane-Canada'[0H AH Supply Air (F)]>100,'GAHP-Trane-Canada'[0E Hyd Supply Tank (F)]<100).

I am trying to create a second column which slightly changes the filter which would look like this;

DHW Input (mmBTU) =

CALCULATE('GAHP-Trane-Canada'[GAHP Gas Input (mmBTU)],

'GAHP-Trane-Canada'[GAHP Gas Use DELTA (cf)]>0,'GAHP-Trane-Canada'[0H AH Supply Air (F)]>100,'GAHP-Trane-Canada'[0E Hyd Supply Tank (F)]>100)

I can successfully create the first column, but by creating the second I have a circular dependency. I was wondering if there is a way to create both columns without a circular dependency since all I am doing is filtering a measure.

[v-eachen-msft · Accepted solution]

Hi @jstein91694 ,

You could refer to this blog: https://www.sqlbi.com/articles/understanding-circular-dependencies/

“Nevertheless, by using CALCULATE, we are asking to transform the current row context into a filter context and this modifies the dependency list.”

You could edit your both columns with FILTER().

SH Input (mmBTU) =
CALCULATE (
    'GAHP-Trane-Canada'[GAHP Gas Input (mmBTU)],
    FILTER (
        'GAHP-Trane-Canada',
        'GAHP-Trane-Canada'[GAHP Gas Use DELTA (cf)] > 0
            && 'GAHP-Trane-Canada'[0H AH Supply Air (F)] > 100
            && 'GAHP-Trane-Canada'[0E Hyd Supply Tank (F)] < 100
    )
)

DHW Input (mmBTU) =
CALCULATE (
    'GAHP-Trane-Canada'[GAHP Gas Input (mmBTU)],
    FILTER (
        'GAHP-Trane-Canada',
        'GAHP-Trane-Canada'[GAHP Gas Use DELTA (cf)] > 0
            && 'GAHP-Trane-Canada'[0H AH Supply Air (F)] > 100
            && 'GAHP-Trane-Canada'[0E Hyd Supply Tank (F)] > 100
    )
)