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

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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 367 members 3,791 guests
Please welcome our newest community members: