Slow perfomance FILTER function

[Sang]

Hi,

I have the following data model. DimOrder table has 1,5 million rows and FactOrderlines has 6 million rows. I need to create a measure where I calculate the number of unique order numbers where there is a Yes in the YesNo column. I am facing some unexpected performance issues, which I don’t understand. I hope someone can explains to me what is happening.

I have tried using the following measure:

Unique Order Number =
CALCULATE (
    DISTINCTCOUNT ( FactOrderlines[Order Number] ),
    FILTER ( DimOrder, DimOrder[YesNo] = "Yes" )
)

The measure above performs terrible when I add Delivery Country from the Order dimension to my (table) visualization.

A solution that seems to work is to add a separate YesNo dimension table to the data model and create a relationship between the YesNo dimension table and Order dimension table.

So, I can create the following alternative measure:

Unique Order Number Alternative =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( FactOrderlines[Order Number] ),
    FILTER ( DimYesNo, DimYesNo[DimYesNoKey] = "Yes" )
)

The alternative measure is more than 10 times faster.

I have read a lot of articles that the star schema is the preferred design in Power BI and creating a relationship between two dimensions should be avoided. However, the measure for unique orders performs better when I add a YesNo dimension to the data model. Why?

In the future I have to add more dimensions to the data model. An example is Order Type and Order Type Category. My initial plan was to add those two columns to the Order dimension table. Now I am wondering if it will be better to create a separate Order Type dimension table and create a relationship between the Order Type dimension table and Order dimension table. Could you give me any advice?

Thank you in advance!

[edhans · Accepted solution]

Don't use FILTER to filter a table like that, it can be very slow.

You can filter this way:

Unique Order Number =
CALCULATE (
    DISTINCTCOUNT ( FactOrderlines[Order Number] ),
    FILTER (
        VALUES ( DimOrder[YesNo] ),
        DimOrder[YesNo] = "Yes"
    )
)

This is filtering a column of distinct values, and that column has only 2 values (or maybe 3 if there are blanks). 

Better yet, just use a predicate like this:

Unique Order Number =
CALCULATE (
    DISTINCTCOUNT ( FactOrderlines[Order Number] ),
    DimOrder[YesNo] = "Yes"
)

Internally, this gets rewritten to this:

Unique Order Number =
CALCULATE (
    DISTINCTCOUNT ( FactOrderlines[Order Number] ),
    FILTER (
        ALL ( DimOrder[YesNo] ),
        DimOrder[YesNo] = "Yes"
    )
)

Which is still filtering a column, not an entire table.

[Sang]

Thank you very much for your reponse. I have tried both of your suggestions. The first suggestion gives the expected result. I am just wondering why the second option is the preffered one:

Unique Order Number =
CALCULATE (
    DISTINCTCOUNT ( FactOrderlines[Order Number] ),
    DimOrder[YesNo] = "Yes"
)

When I use the above measure and add the Yes/No dimension to a table visualization, the result will be as follows:

YesNo	Unique Order Number
No	750.000
Yes	750.000

[edhans]

Because the way CALCULATE works it is replacing the filter in that column with "Yes", which can be problematic depending on how the table is laid out. 

So the best practice would be to use this:

Unique Order Number =
CALCULATE(
    DISTINCTCOUNT( FactOrderlines[Order Number] ),
    KEEPFILTERS( DimOrder[YesNo] = "Yes" )
)

This  will prevent the override of the filter on that column. 

You should read this article. It is a fantastic overview, and pay special attention to the Product[Color] = "Red" section as it is exactly the issue you saw without the KEEPFILTERS. Introducing CALCULATE in DAX - SQLBI