cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmberM Regular Visitor
Regular Visitor

Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is used

I am receiving an error message in calculation: "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

How do I fix the following formula so that I can create a time period slicer for Last Week, Last 4 Weeks, Last 12 Weeks? My data is at a week number level, not a daily level.

 

Sales[Week] = 201804, 201803, 201802, 201801, etc

 

DatePeriod = 
 UNION(
     ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-1),Sales[Week]),"Period","Last Week"),
     ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-2),Sales[Week]),"Period","Last 2 Weeks"),
     ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-4),Sales[Week]),"Period","Last 4 Weeks"),
     ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-12),Sales[Week]),"Period","Last 12 Weeks"),
      ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales),Sales[Week]),"Period","All Other")
 )

This is the original calculation that I am trying to modify to rollup at a week number level:

http://analyticsavenue.com/power-bi-timeperiod-slicer-for-last-7-dayslast-30-days/

DatePeriod = 
UNION (   
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-07+1,today()) ), 'Dates'[Date]),"Period","Last 07 Days")  ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-14+1,today()) ), 'Dates'[Date]),"Period","Last 14 Days") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-30+1,today()) ), 'Dates'[Date]),"Period","Last 30 Days") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-90+1,today()) ), 'Dates'[Date]),"Period","Last 90 Days") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates'), 'Dates'[Date]),"Period","Overall") 
)

Any assistance is a appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

Ah, OK.  It's a calculated table, not a measure.  You enter the formula in the status bar when creating a table...

 

image.png

 

Then you use the Period field in your filter...

image.png

And set up the relationship to the calculated table...

image.png

This was your problem all along I think.  Your original formula is actualy fine if you do it like this.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

Hi @AmberM

 

Try replacing MAX with LASTDATE

AmberM Regular Visitor
Regular Visitor

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

Unfortunately that didn't work either. This is the error message I received. The data is based on a yyyyww level. Good idea though!

 

Error Message.jpgnew error message with LASTDATEORIGINALERRORMESSAGE.pngoriginal error message with MAX

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

Yes, this is an error because CALCULATETABLE is expecting a filter and you are passing a single value.  You need to create a filter.

Modifying the original example (which I can test)...

 

WeekPeriod = 
VAR d0 = TODAY()
VAR w0 = VALUE(YEAR(d0) & FORMAT(WEEKNUM(d0), "00"))
RETURN
UNION (   
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] = w0) ), 'Date'[Date]),"Period","Last Week")  ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 1) ), 'Date'[Date]),"Period","Last 2 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 -3) ), 'Date'[Date]),"Period","Last 4 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 11) ), 'Date'[Date]),"Period","Last 12 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall") 
)

 

You can control the sort order of your filter by adding a sort key column...

WeekPeriod = 
VAR d0 = MAX(FactInternetSales[TransDate])
VAR w0 = VALUE(YEAR(d0) & FORMAT(WEEKNUM(d0), "00"))
RETURN
UNION (   
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] = w0) ), 'Date'[Date]),"Period","Last Week", "sortKey", 0)  ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 1) ), 'Date'[Date]),"Period","Last 2 Weeks", "sortKey", 1) ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 -3) ), 'Date'[Date]),"Period","Last 4 Weeks", "sortKey", 2) ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 11) ), 'Date'[Date]),"Period","Last 12 Weeks", "sortKey", 3) ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall", "sortKey", 4) 
)

...and selecting the sort order for the Period column

image.png

 

This doesn't handle year boundaries however, so a better strategy is to add a column for the start date of the current week using

 

VAR d0 = MAX(FactInternetSales[TransDate])
VAR wd0 = d0 - WEEKDAY(d0, 2) + 1
VAR _date = ADDCOLUMNS('Date', "Week Starting", [Date] - WEEKDAY([Date], 2) + 1)
RETURN
    UNION (   
        ADDCOLUMNS( FILTER(_date, [Week Starting] = wd0),"Period","Last Week", "sortKey", 0)  ,
        ADDCOLUMNS(FILTER(_date, [Week Starting] >= wd0 - 7),"Period","Last 2 Weeks", "sortKey", 1) ,
        ADDCOLUMNS( FILTER(_date, [Week Starting] >= wd0 - 14),"Period","Last 4 Weeks", "sortKey", 2) ,
        ADDCOLUMNS( FILTER(_date, [Week Starting] >= wd0 - 21),"Period","Last 12 Weeks", "sortKey", 3) ,
        ADDCOLUMNS( _date,"Period","Overall", "sortKey", 4) 
    )

 The relationships are the same idea as the original...

 

 image.png

AmberM Regular Visitor
Regular Visitor

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

I really, really appreciate the time you are taking to assist me! 

Now I get a different error message once I modify your calculation. I have included a link to the practice database I am working with. All of this is based on a custom fiscal calendar year that begins, for example 01/28/16 - 01/26/17 (FY 2017).

 

This is why I am struggling to use the DAX Date/Time and Time Intelligence functions.

 

 

Sample data set:

https://1drv.ms/u/s!AoY5sA-v6cUciEOYS-NvMrcINYu1

 

New calculation error:

WeekPeriodError.png

 

Here is the calculation I used:

WeekPeriod = 
Var w0 = MAX(Sales[Week])
RETURN
UNION (   
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] = w0) ), Sales[Week]),"Period","Last Week")  ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 - 1) ), 'Sales'[Week]),"Period","Last 2 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 -3) ), 'Sales'[Week]),"Period","Last 4 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 - 11) ), 'Sales'[Week]),"Period","Last 12 Weeks") ,
   ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales'), Sales[Week]),"Period","Overall")
)

 

 

 

Highlighted

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

Ah, OK.  It's a calculated table, not a measure.  You enter the formula in the status bar when creating a table...

 

image.png

 

Then you use the Period field in your filter...

image.png

And set up the relationship to the calculated table...

image.png

This was your problem all along I think.  Your original formula is actualy fine if you do it like this.

View solution in original post

AmberM Regular Visitor
Regular Visitor

Re: Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is

It figures I was overlooking something so simple! Thank you for all of your assistance! Works perfectly now!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 58 members 1,287 guests
Please welcome our newest community members: