Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Arentir
Resolver III
Resolver III

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 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. 

Arentir_0-1617290428634.png

 

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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.

v-yangliu-msft_0-1617687576369.png

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

v-yangliu-msft_1-1617687576374.png

 

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.

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

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.

v-yangliu-msft_0-1617687576369.png

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

v-yangliu-msft_1-1617687576374.png

 

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
Super User
Super User

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.

     

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

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.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors