Selecting in memory table by selected values in other column

[arnesbeganovic]

Hi,

I have concaternated table from two fact tables like at image bellow. What I try to calculate is Measure1 sum when someone selects WKTour=2 for example. In this scenario it returns 0 because those two rows contains 0 in that column. But if you look carefully, there are same WKOrders which have values in that column. So basically, when someone selects WKTour=2 I would need to identify WKOrder IN (3,4) and take sum from Measure1 when wkorder = 3 or 4. In this scenario it is 12+13=25. 

As you understand, it is two concaternated fact tables where WKTour information exists in one of them. I need to find all orders from Settlement table, use that list and apply it to Order table and get sum as someone selected those orders. I know it can be done in SQL but I need to solve it in DAX.

I tried to create in memory table but do not know how to transfere selected values to in memory table. Not even sure if this is possible. Pure logic says it should...

[Anonymous]

@arnesbeganovic 

Thank you for sharing your solution, this will definitely helps others with same questions.

[arnesbeganovic · Accepted solution]

It took me a bit, but I found the answer that I need and want to share it in case someone else needs it or I need it later on 😄

Removefilters did it's job:

Measure = sumx(VALUES(DimOrder[OrderId]),CALCULATE(sum(FactTable[Measure1]),REMOVEFILTERS(DimTour[TourName])))