Help Filtering value based on another table column

Anything related to PowerPivot and DAX Formuale
xxenoss
Posts: 1
Joined: Fri May 22, 2020 1:50 am

Help Filtering value based on another table column

Postby xxenoss » Fri May 22, 2020 2:00 am

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())

[url]http://prntscr.com/sl6j05[/url]

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())

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron