Filter Based on another table

[Anonymous]

Here is the matrix visual I am creating. The problem is that my measure for Next Milestone Budget is returning blanks.

Here is the model view of the tables in question to create this visual. ProjectID comes from #Success - Project List, - Next Milestone Due Date is a measure based in DimMilestone, relying on only data from that table.

Here is the DAX for Next Milestone Budget. Milestone Date VAR is the same code to generate - Next Milestone Due Date in the visual above.

Budget comes from #Support - Tasks, but I need to filter that table based on the next milestone date.

If I leave out that second filter in the calculate, I get a sum of all budget points for the project of the task type I'm filtering for. But when I try to add the filter for Milestone, it returns all blanks.

- Next Milestone Budget = 
VAR 
MilestoneDate = 
    CALCULATE(
        MIN(DimMilestone[EndDate]),
        DimMilestone[MilestoneStatus] = "Pending"
    )

RETURN 
CALCULATE(
    SUM('#Support - Tasks'[TaskBudget]),
    '#Support - Tasks'[TaskType] = "Software Minor Update",
    '#Support - Tasks'[MilestoneEndDate] = MilestoneDate
)

Any help would be appreciated, thanks very much in advance.

[Anonymous]

@tamerj1 You have been able to help me out with questions before, would you be willing to take a look at this? Thank you very much!

[tamerj1]

@Anonymous 
What are the columns involved in th erelationships? Please provide details.

[Anonymous]

Thanks for responding. 

ProjectMilestoneEndDate is a unique column generated from combining the project id,  milestone name, and milestone end date as the milestones don't have a unique ID.

Here is some sample data:

DimMilestone:

#Support - Tasks:

#Success - Project List:

[tamerj1]

Hi @Anonymous 

please try

- Next Milestone Budget =
VAR MilestoneDate =
CALCULATE (
MIN ( DimMilestone[EndDate] ),
DimMilestone[MilestoneStatus] = "Pending"
)
RETURN
CALCULATE (
SUM ( '#Support - Tasks'[TaskBudget] ),
'#Support - Tasks'[TaskType] = "Software Minor Update",
'#Support - Tasks'[MilestoneEndDate] = MilestoneDate,
ALL ( DimMilestone )
)

[Anonymous · Accepted solution]

Thanks for this. I was messing around with the relationships and was able to get it workign with a relationship directly to the project table from tasks instead of through the milestone table. I'll try this out though and see if it would work without changing the relationships.