CODEWITHSUNDEEP
powerbidax
justinmalin
justinmalin

Reputation: 13

Calculate measures for two different months from two related date tables

I have two identical date tables, "Ref Dates A" and "Ref Dates B", both related to my fact table. I want to create two measures so that in a visual, I can select a month from "Ref Dates A" and another month from "Ref Dates B" and display the corresponding values from my fact table for those two different months.

The problem is that when I select the first month, my fact table is filtered on that month, so the second measure results in a null value. How can I create measures that bypass this cross-filtering and allow me to display values for two independently selected months?

I tried the ALL function, the REMOVEFILTERS and ALLEXCEPT functions in my Calculate(sum(Value), but maybe I don't use them correctly

Upvotes: 1

Views: 35

Answers (1)

Nick A
Nick A

Reputation: 1124

You can use CROSSFILTER() to turn off the other relationships in your measure.

Measure A:

CALCULATE(SUM('Table'[Value]), //Sums values
    CROSSFILTER('Ref Dates B'[Date], 'Table'[Date], None) //Removed the relationship between Table and Ref Dates B
)

Measure B:

CALCULATE(SUM('Table'[Value]), //Sums values
    CROSSFILTER('Ref Dates A'[Date], 'Table'[Date], None) //Removed the relationship between Table and Ref Dates A
)

Upvotes: 0

Related Questions