Reply
Highlighted
Member
Posts: 96
Registered: ‎06-17-2017

Calculated Table doesn't filter properly in ADDCOLUMNS

Why does this work...

 

Date = 
VAR d0 = MAX(FactInternetSales[TransDate])
RETURN
ADDCOLUMNS(CALENDAR("2013-Jan-01",d0), "Week Starting", [Date] - WEEKDAY([Date], 2) + 1)

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

and this doesn't?

 

Date = 
VAR d0 = MAX(FactInternetSales[TransDate])
RETURN
CALENDAR("2013-Jan-01",d0)

WeekPeriod = 
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( CALCULATETABLE(_date , FILTER(_date, [Week Starting] = wd0) ),"Period","Last Week", "sortKey", 0)  ,
        ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 7) ),"Period","Last 2 Weeks", "sortKey", 1) ,
        ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 14) ),"Period","Last 4 Weeks", "sortKey", 2) ,
        ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 21) ),"Period","Last 12 Weeks", "sortKey", 3) ,
        ADDCOLUMNS( CALCULATETABLE(_date),"Period","Overall", "sortKey", 4) 
    )

You can try it by modifying this example

 

Super Contributor
Posts: 663
Registered: ‎01-14-2018

Re: Calculated Table doesn't filter properly in ADDCOLUMNS

Hi @RobertSlattery,

 

In your scenario, what did you mean that your second formula did not work? Did you get any error message? Or you mean that the result you got is not what you want? Then what is your desired result?

 

I have made some test in your shared file. It seems like the expression works for me.

 

Please refer to Page 2: https://1drv.ms/u/s!AlqSnZZUVHmshBcrpJQmKwSHQRpo

 

Thanks,
Xi Jin.

Member
Posts: 96
Registered: ‎06-17-2017

Re: Calculated Table doesn't filter properly in ADDCOLUMNS

Hi @v-xjiin-msft, if you look closely at your example you will see that the filter is not filtering.  All dates are in all categories.  This is the same problem I have (although I worked arround it).

 

The desired result is...

image.png

 

If you look closely at the other table, you will not see this transition.

 

These filters are not filtering at all...

image.png

 

 

 

The result of the boolean expressions is always true.