What is wrong with this formula!

DAX DAX Calculations
1

I want to create calculated column based on the first week sales.

FirstWeekSale =
CALCULATE (
Sales[Sales Amount],
FILTER (
‘Sales’,
AND (
‘Sales’[Order Date] >= ‘Product’[FirstSalesDate],
‘Sales’[Order Date] <= ‘Product’[FirstSalesDate] + 7
)
)
)

Above formula calculate more sales than if i put just Sales[Sales Amount] Measure in calculated column like below:

Calculatedcolum Sale Amount := Calculate(Sales[Sales Amount])

What is wrong with first formula. Shouldn’t it restrict rows and answer should be less than from second simple formula?

I know the reason and alternate formula now:), but I hope it will be interesting use case for someone to understand inner working of DAX.

2

You want to use a dedicated Date Table. Then use that table in your filter statement. You really dont ever want to use your Fact tables in Filter

1 Like
3

So what is the reason and alternate formula?

4

Hi @Nick_M and @mickeydjw ,

Yes, thats right that I should never try to use fact table in filter expression to filter records. Let me break down what’s happening here. It took me some time to understand and it is related to expanded table. Normally fact table in start schema is expanded table of all associated tables. Below article can help you to understand expanded table better:

Anyhow, rule of thumb should be to avoid fact table in filter argument. Now i explain reason of wrong calculation.

FirstWeekSale =
CALCULATE ( — first it will change row context to filter context and it will filter by current product.
Sales[Sales Amount],
FILTER ( — this filter table will bring all records for one week. Since it is fact table, so it had ProductID as column as expanded table. So, it will override initial filter created by row context and so total sales will be calculated for entire week and not only for that particular product.
‘Sales’,
AND (
‘Sales’[Order Date] >= ‘Product’[FirstSalesDate],
‘Sales’[Order Date] <= ‘Product’[FirstSalesDate] + 7
)
)
)

Now, I explained reason above, my formula could be right if I avoid overriding filter context. I can do it in following ways.

  1. By using keepfilter: ( It will help to avoid override of existing filter)

FirstWeekSale =
CALCULATE (
Sales[Sales Amount],
KEEPFILTER(FILTER (
‘Sales’,
AND (
‘Sales’[Order Date] >= ‘Product’[FirstSalesDate],
‘Sales’[Order Date] <= ‘Product’[FirstSalesDate] + 7
)
)
)
)

  1. By using column in filter argument instead of all fact table: ( In this case, it will not be expanded table but only column, so initial filter on productID will not be overwritten.)

FirstWeekSale =
CALCULATE (
Sales[Sales Amount],
FILTER (
ALL(‘Sales’[Order Date]),
AND (
‘Sales’[Order Date] >= ‘Product’[FirstSalesDate],
‘Sales’[Order Date] <= ‘Product’[FirstSalesDate] + 7
)
)
)

I hope that it will give us clear picture that why we should avoid fact table in filter argument. Fact table can be very useful in filter argument(AS bridge table), but only if we know exact usage of it. But again rule of thumb is to try to avoid fact table in filter argument.

1 Like
5

Thanks for the explanation.

6

@hafizsultan
Good stuff here for sure! Expanded tables can get a little complicated and just takes a little bit of time before it starts to click. That’s the whole, “Start thinking like DAX” saying that just takes time. Table filters can be a great thing but they can lead to this type of complexity, so always best to use columns and not table whenever possible.

1 Like