cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

ALL (Column) behavior issue

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: ALL (Column) behavior issue

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Highlighted
Memorable Member
Memorable Member

Re: ALL (Column) behavior issue

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

Highlighted
Super User IV
Super User IV

Re: ALL (Column) behavior issue

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: ALL (Column) behavior issue

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

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper I
Helper I

Re: ALL (Column) behavior issue

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.

Highlighted
Helper I
Helper I

Re: ALL (Column) behavior issue

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
    )
Highlighted
Super User IV
Super User IV

Re: ALL (Column) behavior issue

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors