Hi everyone I have the next problem
Table1 contains Date, LacationA, LocationB, Duration, ValueB
Table2 contains Date, Type, LocationB, Duration, ValueA
My final table looks like this. Columns Date, LocationB and Duration, are from Bridge tables that connects Table1 & Table2.
And ValueA is function ValueA=IF(SUM(Table1[ValueB])>0, SUM(Table2[ValueA]), BLANK())
Lets say I need just Sum of ValueA for this setup. Tipically I would do it like this
Measure = IF(SUM(Table1[ValueB])>0, CALCULATE([ValueA], ALLSELECTED(Duration[Duration])), BLANK())
The problem with it is since there is no LocationA column in Table2, it summarizes ValueA for all the Durations available for specific Date & LocationB in Table2. As you can see in the screenshot result is 856, instead of 728, Cause in Table2 there are additional Durations, that aren’t present in Table1
So I need to filter Durations to those that are available in Table1 for specific Date, LocationA and LocationB.
I tried to do it like this, but it doesn’t work
Measure:=IF([Sum of ValueB]>0,
CALCULATE([ValueA], ALLSELECTED(Duration[Duration]), (Table1[LocationA])), BLANK())
Anything related to PowerPivot and DAX Formuale
1 post • Page 1 of 1
Who is online
Users browsing this forum: No registered users and 4 guests