cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculated column to get value from another table ignoring relationship

I have two tables:

Milestone

 Milestone Title Finish Start 1/1/2024 Milestone1 2/1/2024 Milestone2 3/1/2024 Finish 4/1/2024

StandardMilestone

 StandardMilestoneTitle MilestoneSort Start 1 Milestone1 2 Finish 3

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 Title Finish StartSort Start 1/1/2024 1 Milestone1 2/1/2024 1 Milestone2 3/1/2024 1 Finish 4/1/2024 1

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

 Milestone Title Finish StartSort Start 1/1/2024 1 Milestone1 2/1/2024 Milestone2 3/1/2024 Finish 4/1/2024

Here's my formula:

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

Where are my DAX skills failing me?
2 REPLIES 2
Super User

@jsangerman I can't seem to replicate this. See attached PBIX below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

 StandardMilestoneTitle MilestoneTitle MilestoneSort StandardStart Start 1 StandardMilestone1 Milestone1 2 StandardFinish Finish 3

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors