Forum Discussion

jsangerman's avatar
jsangerman
Helper II
2 years ago

Calculated column to get value from another table ignoring relationship

I have two tables:

 

Milestone

Milestone TitleFinish
Start1/1/2024
Milestone12/1/2024
Milestone23/1/2024
Finish4/1/2024

 

StandardMilestone

StandardMilestoneTitleMilestoneSort
Start1
Milestone12
Finish3

 

There's a relationship between these two (StandardMilestoneTitle --> Milestone Title), though you'll notice that not every value for Milestone Title has a matching value in StandardMilestone. I want to add a column to Milestone with the MilestoneSort value for the row in StandardMilestone where Title = "Start." In other words:

 

Milestone TitleFinishStartSort
Start1/1/20241
Milestone12/1/20241
Milestone23/1/20241
Finish4/1/20241

 

What I'm getting is this, where only the row in Milestone where Title = Start gets the calculation:

Milestone TitleFinishStartSort
Start1/1/20241
Milestone12/1/2024 
Milestone23/1/2024 
Finish4/1/2024 

 

Here's my formula:

CALCULATE( MIN( 'StandardMilestone'[MilestoneSort] ), FILTER( ALL('StandardMilestone'), 'StandardMilestone'[StandardMilestoneTitle] = "Start") )
 
Where are my DAX skills failing me?
Need Help

2 Replies

    • jsangerman's avatar
      jsangerman
      Helper II
      2 years ago

      Turns out I had the relationship cross-filter going in both directions. When I switch it to one direction, it works as expected.

       

      However, when I tried the same thing on your example, it didn't work the same. The calculated column works as expected no matter the cross-filter direction. There's actually one difference I hadn't thought of, but is causing the changing behavior. My StandardMilestone table actually looks like this:

       

      StandardMilestone

      StandardMilestoneTitleMilestoneTitleMilestoneSort
      StandardStartStart1
      StandardMilestone1Milestone12
      StandardFinishFinish3

       

      The relationship is MilestoneTitle --> Milestone Title, and the column formula is:

       

      CALCULATE( MIN( 'StandardMilestone'[MilestoneSort] ), FILTER( ALL('StandardMilestone'), 'StandardMilestone'[StandardMilestoneTitle] = "StandardStart") )

       

      I redid your file to match this:  Milestones.pbix

       

      Can you explain why it doesn't work when the cross-filtering goes in both directions?