topic Re: Filter Based on another table in DAX Commands and Tips

Filter Based on another table

Anonymous — Wed, 12 Apr 2023 14:53:25 GMT

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.

Re: Filter Based on another table

Anonymous — Wed, 12 Apr 2023 20:41:20 GMT

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!

Re: Filter Based on another table

tamerj1 — Thu, 13 Apr 2023 05:42:14 GMT

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

Re: Filter Based on another table

Anonymous — Thu, 13 Apr 2023 13:26:39 GMT

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:

Re: Filter Based on another table

tamerj1 — Thu, 13 Apr 2023 15:21:24 GMT

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 )
)

Re: Filter Based on another table

Anonymous — Thu, 13 Apr 2023 18:36:39 GMT

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.