Forum Discussion

Sang's avatar
Sang
Frequent Visitor
2 years ago
Solved

Slow perfomance FILTER function

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 whe...
Need Help
  • edhans's avatar
    edhans
    2 years ago

    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.

edhans's avatar
edhans
Community Champion
2 years ago

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's avatar
    Sang
    Frequent Visitor
    2 years ago

    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:

    YesNoUnique Order Number
    No750.000
    Yes750.000

     

    • edhans's avatar
      edhans
      Community Champion
      2 years ago

      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