DAX Calculated Column with FILTER won't respond to Slicers

[ChemEnger]

I have a table of Work Orders (All Work Orders) and I would like to see date-by-date how many were in an open (or overdue) state.

Each Work Order has (amongst other things):

• [WO Number] - String, Index
• [Department] - String
• [Orgination Date/Time] - Date/Time
• [Completion Date/Time] - Date/Time
• [WO Complete] - Boolean

I also have a WO Type from a Dimension Table (WO Types)

Finally, I have built a Date Table to act as the X-axis of a chart:

DateTable - CALENDARAUTO(12)

And added calculated columns.  For the Open WOs column in DateTable, I have:

Open WOs = 
 CALCULATE(
    COUNTROWS('All Work Orders'),
    FILTER(ALLSELECTED('All Work Orders'),'All Work Orders'[Origination Date/Time]<=DateTable[Date]),
    FILTER(ALLSELECTED('All Work Orders'),'All Work Orders'[Completion Date/Time]>=DateTable[Date] || NOT('All Work Orders'[WO Complete]))
    )

Which works, but does not respond to other slicers on the report, which I have red-underlined in the Model view screenshot below. I had orignally tried just the FILTER command and have tried also KEEPFILTERS but nothing that I do seems to work!

Model View of All Work Orders & DateTable

[amitchandak]

@ChemEnger , New table, and new columns will not respond to the slicer filter. You need to create a measure.

[ChemEnger]

Thanks for the super-quick reply @amitchandak,

Where / how would the measure go in that case please?

[amitchandak]

@ChemEnger , this blog should help.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[ChemEnger]

So - very nearly there thanks @amitchandak!

Using your blog as a guide, I added two inactive relationships between DateTable and All Work Orders and added the measure [Open WOs] to the All Work Orders table:

Revised Model view with relationship between DateTable and All Work Orders

The measure is as below

Open WOs = 
    CALCULATE(
       COUNTx(
          FILTER('All Work Orders',
          'All Work Orders'[Origination Date/Time]<=MAX('DateTable'[Date]) && ('All Work Orders'[WO Complete]=FALSE() 
          || 'All Work Orders'[Completion Date/Time]>MAX('DateTable'[Date]))),('All Work Orders'[WO Number])
          )
       )

I had to make one small adjustment and change the [Date] Column in DateTable to Date instead of Date/Time, otherwise it was looking at 00:00:00 so out by one day. 

This measure now works with the slicers 🙂

The only question now (please!) is, is there any way of getting the maximum for any period when I Drill up in the chart (this was easy before as I could just tell the column to summarise by Maximum but this isn't possible with a measure) rather than (I am guessing) the value for the last day of the month.

As an example, here's the difference on a month-by-month basis of the two:

Comparison between number of records using Calculated Column against Measure