Forum Discussion

Arentir's avatar
Arentir
Icon for Resolver III rankResolver III
5 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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's avatar
    Arentir
    Icon for Resolver III rankResolver III
    5 years ago

    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's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      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.