cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Is this the normal behavior? - Puzzled by result

Hi all,

 

I have a simple scenario (see pbix here😞  

1. Tables:

     a. A one-column CalendarTable with the first ten days of 2015

     b. A SalesTable with two columns:

            [Date]   --> Same first ten days of 2015

            [Amount] 

2. Matrix visual:

     a. SalesTable[Date] in rows

     b. CalendarTable[Date] in a slicer

     c. In values:

         c1. Sales[Amount]

         c2.  SalesMeasure = CALCULATE(SUM(SalesTable[Amount]), ALL(CalendarTable[Date]) )

         c3.  SalesMeasure2 = CALCULATE(SUM(SalesTable[Amount]), ALL(SalesTable[Date]) )  

 

 Now, I would expect both measures, [SalesMeasure]  and [SalesMeasure2], to yield the same results in every row of the matrix, i.e the sum of all rows in SalesTable[Amount]. This is not the case. It appears that the slicer still has an effect on [SalesMeasure2]. I do not understand why this is so. Since we are using ALL(SalesTable[Date]) I would expect filters to be removed. The slicer is active and it propagates down to SalesTable, but that should be neutralized by the ALL(SalesTable[Date]) anyway... 

 

Can anybody explain this result? Is this the normal behavior? 

 

Many thanks  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Is this the normal behavior? - Puzzled by result

Hi @AlB,

 

on the expanded version of the SalesTable (which includes the Calendar Table as you've mentioned) now you have two columns 'Date', the native one of 'Sales' plus the one coming from 'Calendar'.

Therefore on the measure:

 

CALCULATE(SUM(SalesTable[Amount]), ALL(SalesTable[Date]) )  

 

the filter coming from the Slicer (on which you 're using Calendar[Date]) remains active because you're removing the filter only from the 'Date' column that is native to Sales

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: Is this the normal behavior? - Puzzled by result

Hi @AIB this has to do with the concept of expanded tables with which the table on the many side 'inherits' the columns from the one side. You are removing the filter on the column Sales[Date] but the filter on the column Calendar[Date] is still active, so you need to remove that filter as well in your measure.

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Super User III
Super User III

Re: Is this the normal behavior? - Puzzled by result

Hi @LivioLanzo

Thanks very much for your prompt response. I do not quite agree with what you say but it certainly put me in the right direction. Here's what I think is happening. Let me know what your take is:

 

I was using ALL(SalesTable[Amount]). With this we're removing the filters on that column but the other column in SalesTable, SalesTable[Amount] will still be crossfiltered by the slicer. To prevent this, we need to do the ALL( ) on the whole SalesTable. This measure works with the slicer on CalendarTable[Date] still active:

 

 

SalesMeasure3 = CALCULATE ( SUM ( SalesTable[Amount] ); ALL ( SalesTable ) )

 

I do not think expanded tables play a role here since the expanded table of SalesTable includes CalendarTable but the expanded table of CalendarTable does not include SalesTable.  

 

What do you reckon?  

 

Highlighted
Solution Sage
Solution Sage

Re: Is this the normal behavior? - Puzzled by result

Hi @AlB,

 

on the expanded version of the SalesTable (which includes the Calendar Table as you've mentioned) now you have two columns 'Date', the native one of 'Sales' plus the one coming from 'Calendar'.

Therefore on the measure:

 

CALCULATE(SUM(SalesTable[Amount]), ALL(SalesTable[Date]) )  

 

the filter coming from the Slicer (on which you 're using Calendar[Date]) remains active because you're removing the filter only from the 'Date' column that is native to Sales

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Highlighted
Super User III
Super User III

Re: Is this the normal behavior? - Puzzled by result

@LivioLanzo

I see what you mean. You're right. I'll mark the post as solution when the feature works again.

Thanks   

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors