CALCULATE FILTER respects some filters but not another (a 'greater than' comparison across 2 tables)

[alicek]

Hello, 

I've got three tables: 

1. Projects table, with one row per project. 
2. FY Budget table, with one row per fiscal year-project combination (each row is the project's budget for that fiscal year).
3. Timesheets table, with one row per project-staff-day (each row is an amont of time billed by a staff person to a project per day).

The projects table filters one-to-many the other two tables. The other two are not related.

I'm trying to calculate two metrics: number of projects "on time" and number of projects "on budget."
Let's choose one, since it is happening in both.

When I create a dax measure for "on time" I used:

Projects On Time = 

CALCULATE(
    DISTINCTCOUNTNOBLANK('MSP: Projects'[ProjectId]), 
        FILTER('MSP: Timesheet', SUM('MSP: Timesheet'[ActualWorkBillable]) > 200), 
        FILTER('MSP: Projects', NOT(ISBLANK(LASTDATE('MSP: Projects'[PlannedDeliverableCompletionDate])))),
        FILTER('MSP: Projects', NOT(ISBLANK(LASTDATE('MSP: Projects'[ActualDeliverableCompletionDate])))),
        FILTER('MSP: Projects', [# Days b/w Plan - Act Deliv Dates] >= -60)
)

For refence, that last filter uses this code:

# Days b/w Plan - Act Deliv Dates = 
IF(
    ISBLANK(LASTDATE('MSP: Projects'[PlannedDeliverableCompletionDate])) || ISBLANK(LASTDATE('MSP: Projects'[ActualDeliverableCompletionDate])),
    BLANK(),
    DATEDIFF(LASTDATE('MSP: Projects'[ActualDeliverableCompletionDate]), LASTDATE('MSP: Projects'[PlannedDeliverableCompletionDate]), DAY)
)

When I drag thse measures as columns in a table, the resulting table CORRECTLY only assigns a "1" to projects that have no blanks in the date fields listed and are early, on, or no more than 60 days past schedule. There are only 5 projects that get a 1. However, at the bottom in the "Total" row, it shows a 7.

There are only two projects that are less than 200 hours. It appears, somehow, for some reason, to be counting the two projects that are less than 200 hours, even though they aren't getting a "1" in the table row.

When I made a measure to label projects as 200+ hours or <200 hours, and dragged it onto the filter pane for the table, it correctly hides those two rows, and the "Total row" at the bottom now correctly shows 5. However, when I try and make a card visual with the PRojects on Time measure, it shows 7 and doesn't let me drag the same measure onto the filter pane that it lets me for the table. 

Can someone explain why it refuses to respect the first filter argument in the calculate field? And why it would total to 7 even though not giving those two rows a 1? Or why the card visual won't let me add a filter pane filter when the table will?

I'm getting the same problem for the "on budget" measure - and thought that might be because I'm trying to add a fitler expression to Calculate that is a greater than with a sum on one column from one table on the left and a sum on one column from another table on the right. It does work when creating a label "meets budget" or "over budget" -- just not when I try to calculate a distinct count of those labelled "meets budget."

How would I fix this for the on budget measure? Can I put an if statement inside a calculate to get around this?

[Ritaf1983]

Hi @alicek 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly