Self Filtering Table with DAX in Tabular Model very slow

Question

I have a Fact-Table containing order positions and the corresponding order number. It has about 250 mio rows.

My Goal is to create a measure which can be used to answer the following question: What's the total revenue of all orders containing product xyz.

I added a table containing all distinct article numbers called Orderfilter with just one column OrderFilter[ArticleNr]. This table shall be used to filter the orders table. This table is not related to the orders table.

Based on this setup I created following measure.

Orderfilter Sum sales:=SUMX(
CALCULATETABLE (
    Orders;
    FILTER (
        Orders;
        CONTAINS (
            CALCULATETABLE (
                VALUES(Orders[OrderNr]);
                FILTER (
                    Orders;
                    CONTAINS (
                        VALUES ( Orderfilter[ArticleNr] );   
                        Orderfilter[ArticleNr];
                        Orders[ArticleNr]
                    )
                )
            ); 
            Orders[OrderNr];
            Orders[OrderNr]
        )
    )
);
Orders[Salesamount]
)

This calculation by itself seems to be working, but it is very slow. When i try to use it in Excel putting hierarchies into the rows it gets timed out.

Is there any possiblity tuning this query?

Regards, Henning Lange

Update: Desired Result Tables (really don't know how to add tables here...)

The Orders table. If i filter for ArticleNr=2 i want to keep the "<- Keep"-rows.

|OrderNr     |ArticleNr  |    
|1           |1          |   <- Keep    
|1           |***2***    |   <- Keep    
|2           |1          |    
|2           |3          |    
|3           |***2***    |   <- Keep

Answer 1

This approach will work only if you select one ArticleNr. Since I am using MAX() function to determine what is the selected value in the filter it will return the maximum selected ArticleNr always.

Consider this sample data:

Orders Table

OrderNr  ArticleNr  Revenue
   1         1        100
   1         2        200
   2         1         50
   2         3         70
   3         2        300
   3         4        200
   4         1         50

Articles

ArticleNr
   1
   2
   3

Create a measure to determine the rows:

Rows :=
SUMX ( FILTER ( Orders, [ArticleNr] = MAX ( Articles[ArticleNr] ) ), 1 )

Now using the Rows measure you can get the Sum Sales:

Sum Sales :=
SUMX (
    FILTER ( Orders, SUMX ( ALLEXCEPT ( Orders, Orders[OrderNr] ), [Rows] ) = 1 ),
    [Revenue]
)

This is a Pivot Table in Power Pivot.