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.

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.