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

Why don't you create the relationship between both tables? it would be more performant than use CONTAINS function in a FILTER. Explicit relationships are always faster than filters. — alejandro zuleta
Hello, thanks for the fast response. I think a relationship would not help me with this problem. If i create a relationship between those tables i would relate on Orders[ArticleNr] to Orderfilter[ArticleNr]. This way i would filter the Orders table based on ArticleNr. But that is not what I want to achieve. I want to filter the Orders table based on OrderNr. "What are the sales of all orders containing articles x,y or z?" With your proposal i could answer the question "What are the sales of the articles x,y or z?". Or am i wrong in this assumption? — Henning Lange
I'd handle it at the source, i.e, creating a common column in the Orders and the Orderfilter tables. Edit your question to include sample data and expected result. — alejandro zuleta
In that example the first row is included because the OrderNr value is the same as the second row which does have the selected ArticleNr (Number 2), right? Is that the rule to determine the filtered rows? — alejandro zuleta

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-01T03:31:56

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 maximun 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.

Let me know if this helps.

Self Filtering Table with DAX in Tabular Model very slow

2 Answers