CALCULATE/ALL - unexpected impact on table visual when column comes from another table

[Arentir]

Hi everyone,

I am trying to understand the unexpected impact of a measure on my table, which give me different result wheter one of the column comes from one table or another

The model is simple. I have two tables: FactSales and Date

I want to show all the fields in Factsales (ID, Date, Product) and "count of all product per day" which is defined as follow:

Count All Product per Day = CALCULATE(COUNTROWS('FactSales'), ALL('FactSales'[Product]))

This works just as expected if all the table fields come from FactSales. However, if I am using Date from DimDate instead. I am getting way more rows: Product will show for every single date, although measure compute correctly. 

What I am interested in here is simply to understand why I am getting this result.

Many thanks for your help

Model available here:

https://1drv.ms/u/s!Ain1dSD1Cch0hS6hfGNxkVL70rwN?e=H67gUO

[Anonymous · Accepted solution]

Hi  @Arentir  ,

1. Count All Product per Day = CALCULATE(COUNTROWS('FactSales'),ALL('FactSales'[Product]))The all() function ignores all filters that have been applied, and returns all the values in all rows or columns in the table.
In Date From Fact, the columns are all from the same table, so the data in the table will be directly presented and calculated.
In the case of Date from Dim, the fields come from different tables, and the relationship between the table and the table is one-to-many,so using the all() function here will return a table containing all products, and each date will be returned once for each product.

2. When you cancel all(), the result will become:

Regarding the all() function, you can view these related contents:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://docs.microsoft.com/en-us/dax/all-function-dax

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Arentir 

In my undestanding,

For instance,

FactDate-1stJan-All Product means A and B (1+1 )

DimDate-1stjan-All Product means A,B,C (1+1+0 )

FactDate- 4thJan-All product means A,A,C -> A,C

DimDate - 4thJan-ALL product means A,B,C

I tried to write as simply as possible.

I hope it helped.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Arentir]

Thanks @Jihwan_Kim , does it make sense to you that for instance C that is not in FactSale for the 1st would show in the table visual if I use dimdate field? 

As far as I understand, dimdate.date is part of the expanded table, I fail to understand why the row shows.

[Jihwan_Kim]

Hi, @Arentir 

Thank you very much for your feedback.

The result of the measure is, remove products filter and count rows.

In my opinion, 

Using Dim-date-table: remove products filter and count rows for the 1st.

My understanding might be wrong, but I sometimes face a similar situation when I use dates from Dim-Date and products from Dim-Products.