Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter Based on another table

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's avatar
    Anonymous
    3 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      tamerj1
      Community Champion

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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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: