topic Re: ALL (Column) behavior issue in DAX Commands and Tips

ALL (Column) behavior issue

KevinW_SDP — Sun, 26 Apr 2020 00:30:55 GMT

I am working on some DAX and it is not working as I expect. I tried using some of the DAX functions to do this, but they weren't quite what I was needing. Also, I am doing this in SSAS which seems to have a very slight difference in support for newer DAX commands.

I am working on creating a Prior Year To Date functionality, but the precanned TOTALYTD doesn't display results when certain filters are applied. Instead, I am trying to use the code below. The main problem I am having is I need to return all of the results from the Sales table with filters still there except for on the date fields as I want to apply a custom date filter as shown in the code below.

Sales Amount PYTD:= RETURN SUMX( FILTER( ALL(Sales[invoiced_date]), YEAR('Sales'[invoiced_date]) = SELECTEDVALUE('- Date'[YearOrder]) - 1 && MONTH('Sales'[invoiced_date]) <= SELECTEDVALUE('- Date'[MonthOfYearNumber]) ), [sales_amount] )

I am using ALL(Sales[invoiced_date]) which according to the Microsoft documentation here, this code should "Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters." I interpret that as by using this in the manner I have, it should return all data in that table but remove the filter from the Sales[invoiced_date] column.

The problem is, when I do this, Visual Studio is throwing a "Semantice Error: Column 'sales_amount' cannot be found or may not be used in this expression." Can anyone give me some insight on what I am doing wrong here?

Thank you in advance.

Re: ALL (Column) behavior issue

vivran22 — Sun, 26 Apr 2020 03:07:59 GMT

Hello KevinW_SDP ,

Please share sample data with expected results (including the filters you were applying and not getting the right results).

Ideally Time Intelligence functions should solve your problem.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

Re: ALL (Column) behavior issue

amitchandak — Sun, 26 Apr 2020 04:08:50 GMT

KevinW_SDP , You can try YTD with following method. Prefer a date calendar do not use Date from fact.

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year)) Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date])) YTD QTY forced= var _max = today() return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max = date(year(today())-1,month(today()),day(today())) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max) YTD QTY forced= var _max = maxx('order',[Order date]) return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max1 =maxx('order',[Order date]) var _max = date(year(_max1)-1,month(_max1),day(_max1)) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Re: ALL (Column) behavior issue

Anonymous — Sun, 26 Apr 2020 08:27:29 GMT

Please, before you start creating DAX and models which are completely wrong and unmanageable, try to watch these first and then adhere to Best Practices. It'll help you stay sane.

https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-month-to-date

Best
D

Re: ALL (Column) behavior issue

KevinW_SDP — Sun, 26 Apr 2020 13:54:21 GMT

Please read my original question. I have not got to the point of publishing this code because it throws an error in Visual Studio as mentioned at the end of the question. The problem is not the results of the query, but the fact that the documentation states one thing and when coding it, it is throwing an error stating it is not being used properly.

Re: ALL (Column) behavior issue

KevinW_SDP — Sun, 26 Apr 2020 14:04:36 GMT

Thank you for the reply. Below is the code that ended up giving me what I am needing.

Sales Amount PYTD:= var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) RETURN CALCULATE ( Sum('Sales'[sales_amount]), DATESYTD( DATEADD('- Date'[Date], -1, YEAR)), '- Date'[Date] <=_max )

Re: ALL (Column) behavior issue

Anonymous — Sun, 26 Apr 2020 14:24:58 GMT

Here is the insight you wanted.

First, sorry to say that but this code is flawed. This piece

var _max = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

will be wrong if you stumble upon a leap year. Check it out.

Secondly,

Sales Amount PYTD := SUMX ( FILTER ( ALL ( Sales[invoiced_date] ), YEAR ( 'Sales'[invoiced_date] ) = SELECTEDVALUE ( '- Date'[YearOrder] ) - 1 && MONTH ( 'Sales'[invoiced_date] ) <= SELECTEDVALUE ( '- Date'[MonthOfYearNumber] ) ), [sales_amount] )

gives you a semantic error because the table ALL( Sales[invoiced_date] ) has only one column: invoiced_date. And you are iterating over it trying to extract [sales_amount] which is not present in it.

I have a question regarding your measure:

Sales Amount PYTD := VAR _max = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) RETURN CALCULATE ( SUM ( 'Sales'[sales_amount] ), DATESYTD ( DATEADD ( '- Date'[Date], -1, YEAR ) ), -- What is the purpose of this filter? '- Date'[Date] <= _max )

What's the purpose of the filter marked with my comment? The one with "... <= _max"? Does it in any way contribute to the calculation? 'Cause I think it does not. Can you please explain? Thanks.

Best
D