Skip to main content
cancel
Showing results for 
Search instead 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

Reply
yogeshk77
Helper I
Helper I

Max from related table

I need to get "Max" of related value from the child table.

 

Here is my data structure 

Parent Table "Stories"

yogeshk77_0-1640943111025.png

 

Child Table "Build Work Item" (Related by Work Item ID field)

yogeshk77_1-1640943161097.png

Now, in the parent table (Stories), I need to get Max of Build ID

For which I have written a measure like this ->

 

yogeshk77_2-1640943322657.png

 

But it's giving me this result (giving same Build ID for all records)

 

yogeshk77_3-1640943400224.png

 

What am I missing?

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @yogeshk77 ,

 

I add some dummy data to the Build WI table.

vstephenmsft_0-1641281807071.png

 

Here's the solution.

1.Relationship is as follows.

vstephenmsft_1-1641281995596.png

 

2.[Build ID] is from Build WI table, and the aggregation operation selects the maximum. [Work Item ID] is from Stories table.

vstephenmsft_2-1641282169515.png

vstephenmsft_4-1641282533769.png

 

 

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

 

vstephenmsft_3-1641282456686.png

 

 

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.

View solution in original post

4 REPLIES 4
yogeshk77
Helper I
Helper I

Thanks @v-stephen-msft 

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.

v-stephen-msft
Community Support
Community Support

Hi @yogeshk77 ,

 

I add some dummy data to the Build WI table.

vstephenmsft_0-1641281807071.png

 

Here's the solution.

1.Relationship is as follows.

vstephenmsft_1-1641281995596.png

 

2.[Build ID] is from Build WI table, and the aggregation operation selects the maximum. [Work Item ID] is from Stories table.

vstephenmsft_2-1641282169515.png

vstephenmsft_4-1641282533769.png

 

 

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

 

vstephenmsft_3-1641282456686.png

 

 

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.

SteveHailey
Solution Specialist
Solution Specialist

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors