Forum Discussion
Max from related table
I need to get "Max" of related value from the child table.
Here is my data structure
Parent Table "Stories"
Child Table "Build Work Item" (Related by Work Item ID field)
Now, in the parent table (Stories), I need to get Max of Build ID
For which I have written a measure like this ->
But it's giving me this result (giving same Build ID for all records)
What am I missing?
Hi yogeshk77 ,
I add some dummy data to the Build WI table.
Here's the solution.
1.Relationship is as follows.
2.[Build ID] is from Build WI table, and the aggregation operation selects the maximum. [Work Item ID] is from Stories table.
3. If you want the measure to display the maximum value, you can create the following measure.
Latest Build ID = CALCULATE(MAX('Build WI'[Build ID]),ALLEXCEPT('Stories',Stories[Work Item Id]))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
If the tables are related by the Work Item ID, why are you using USERELATIONSHIP to relate them on [Iteration] and [Build Iteration]? Also, the second table in the USERELATIONSHIP is 'Build Master' which isn't one of these two tables.
Have you tried taking out the USERELATIONSHIP to leave just the CALCULATE ( MAX ( 'Build WIs'[Build ID] ) ) ? That should work fine if you have an existing active relationship on the Work Item ID columns of these two tables.
Thanks SteveHailey
Actually I made a silly mistake for not analyzing the full data. The result I was seeing was in fact correct. There was no issue.
So, even below works for me .Latest Build ID M =
CALCULATE(MAX('Build WIs'[Build ID]))
Thanks anyways.
Hi yogeshk77 ,
I add some dummy data to the Build WI table.
Here's the solution.
1.Relationship is as follows.
2.[Build ID] is from Build WI table, and the aggregation operation selects the maximum. [Work Item ID] is from Stories table.
3. If you want the measure to display the maximum value, you can create the following measure.
Latest Build ID = CALCULATE(MAX('Build WI'[Build ID]),ALLEXCEPT('Stories',Stories[Work Item Id]))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous
your solution will work too.
Actually I made a silly mistake for not analyzing the full data. The result I was seeing was in fact correct. There was no issue.
So, even below works for me .Latest Build ID M =
CALCULATE(MAX('Build WIs'[Build ID]))
Thanks anyways.
