Nested filter context modification

[Anonymous]

Hey guys. I've stumbled on bit of a pickle.

I've got a simple 'Orders' table containing list of order details specifying [Order #], [Date created], [Date closed] and [Age (days)]:

The [Date created] column is linked with [Date] column in 'Dates' table. Dates table has also a [Week] column which is showing the last date in particular week:

I want to show how many orders were work-in-progress (WIP) each week. An order is considered WIP in the given week if 2 conditions are met:

1. [Date created] is on or before the [Week] date
2. [Date closed] is after the [Week] date.

And I want to visualize it say in a stacked column chart with [Week] column on X axis and WIP count on Y axis.

Essentially there are 2 steps:

1. Get a table with all orders - the filter context coming from the [Week] column in 'Dates' table removed
2. Count rows of the resulting table filtered by the dates as mentioned earlier.

Now, I can do something like this:

WIP count = 
COUNTROWS(
    CALCULATETABLE(
        'Orders',
        'Orders'[Date created]  <= MAX( 'Dates'[Week] ),
        'Orders'[Date closed]   >  MAX( 'Dates'[Week] ) 
    )
)

And it would work were it not for the relationship b/w the 2 tables. The table in formula is not calculated from all the orders but rather from a subset of orders that were created on the given week due to the said relationship.

And that's the problem.

I need to incorporate something like ALLSELECTED('Order'[Date created]'. But when I put it in the formula, it doesn't work.

[lbendlin · Accepted solution]

You could go all cute and use INTERSECT and USERELATIONSHIP

WIP = 
var w = max(Dates[Week])
var cr = CALCULATETABLE(VALUES(Orders[Order #]),Dates[Date]<=w)
var cl = CALCULATETABLE(VALUES(Orders[Order #]),Dates[Date]>w,USERELATIONSHIP(Dates[Date],Orders[Date Closed]))
return countrows(INTERSECT(cr,cl))

,  or you could simply inactivate both relationships.

WIP = 
var w = max(Dates[Week])
return CALCULATE(COUNTROWS(Orders),Orders[Date Created]<=w,Orders[Date Closed]>w) 

[Anonymous]

Thank you!

I eventually made it work. But I like your solution with INTERSECT too.

Interesting idea, though, to inactivate both relationships.