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...
  • 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

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