Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Nested filter context modification

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 c...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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)

     

     

     

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you!

     

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

     

    Interesting idea, though, to inactivate both relationships.