cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

2 REPLIES 2
v-xjiin-msft Super Contributor
Super Contributor

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.

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,571 guests
Please welcome our newest community members: