How to replace two-way cross filter direction with DAX

[Anonymous]

I have the following data model

data model

The following table shows Wind_Generation:

wind generation

I am trying to sum all of 'facilities_wind'[Generator Capacity] per 'calendar'[Date].

How do I solve the problem without turning the cross-filter direction between 'facilities' and 'Wind' to both?

(trying to learn DAX) :^)

[amitchandak]

@Anonymous , facilities_wind is a fact on many sides and will get filtered from date, that does not need cross direction

if you these dimension slicer to filter each other check

https://www.youtube.com/watch?v=cyOquvfhzNM

[Anonymous]

The table below shows the correct results when I cross-filter both ways.

correct results by cross-filtering both ways

where Column 5 = Column 2 / Column 4.

I figured out how to replicate that table when switching to cross-filter one way.

I just need to use the Date column from 'Wind_generation', not 'calendar' , and connect them with RELATED.

However, I am failing to replicate Column 5:

Column 5 incorrect

Formula for column 5 is 

MEASURE CapacityFactor% = 
    VAR GenCpcty = SUMX('Wind_Generation','facilities_wind'[MEASURE_GeneratorCpcty])
    VAR GenCpctyMWh = SUMX('Wind_Generation',GenCpcty * RELATED('calendar'[Hours_Day]))
    VAR ActualGen = CALCULATE(SUM('Wind_Generation'[Value]))
RETURN 100*DIVIDE(ActualGen, GenCpctyMWh)

But when I calculate the measures separately, like the following,

Measure = SUMX('Wind_Generation','facilities_wind'[MEASURE_GeneratorCpcty])
Measure 2 = SUMX('Wind_Generation',[Measure] * RELATED('calendar'[Hours_Day]))
Measure 3 = CALCULATE(SUM('Wind_Generation'[Value]))
Measure 4 = 100 * DIVIDE([Measure 3], [Measure 2])

it works!!

column 6

How can that be?!! The formulas are the same, except that the first one keeps all the measures as VARs.

What's going on?

[Anonymous]

Hi @Anonymous ,

Believe it's something with Row context and filter context.

For example:

measure1 = rankx('table',sum(value))

measure2 = rankx('table',calculate(sum(value)))

tmp = sum(value)

measure3 = rankx('table',[tmp])

measure1<>measure2&&measure3

Measure2=measure3

Hope this blog helps.

https://blog.enterprisedna.co/row-context-and-filter-context-in-a-power-bi-dax-code/ 

Best Regards,

Jay

[Anonymous]

So I tried something that I saw on that link.

When I externalise the 2nd variable, it works.

wrong result

This works

externalize 2nd variable works

Once can notice in the 1st picture above that the three variables are connected by a vertical line, which disappears when I externalise one of the variables.

If I understand correctly, when I invoke [M2] in the 2nd measure, a context transition occurs, which is probably missing from my first measure. However, just applying CALCULATE to SUMX in M3 doesn't solve the problem.

How do I fix VAR GenCpctyMWh without having to externalize the variable in order to apply the correct context transition?

@SpartaBI 

[Anonymous · Accepted solution]

Ha! Solved it, thanks to @Anonymous, my tenacity and this post: Context transition 

The Measure must be written like the following:

M100 = 
    VAR GenCpctyMWh = SUMX (
        ADDCOLUMNS (
            'calendar',
            "@CpctyDay", CALCULATE(SUM('facilities_wind'[Generator Capacity (MW)]), RELATEDTABLE(Wind_Generation))
        ),
        [@CpctyDay]*('calendar'[Hours_Day])
    )

    VAR GenDaily = CALCULATE(SUM('Wind_Generation'[Value]))
RETURN 100 * DIVIDE(GenDaily, GenCpctyMWh)

[Anonymous]

Hey, @amitchandak,

I was wondering if it's possible to write a measure without the aid of a slicer.

When I apply cross-filtering both ways, the measure

MEASURE_GeneratorCpcty = CALCULATE(SUM('facilities_wind'[Generator Capacity (MW)]))

gives the correct result:

correct result

If I switch to one-way filtering, then I get the wrong result:

wrong result

How can I rewrite the MEASURE with one-way filtering?