Dax Measure for Nested Row Contexts

[navinrangar]

Hi all,

I have a database table that looks like this.
 

What i want is to write a dax to calculate the sum of all the secondary_boat_weight where the secondary_boat_id matches the boat_id.

for example,

Taking the first row:

* boat_id is 1.
Now going to the secondary_boat_id column two records match to boat_id.
now corresponding secondary_boat_weight of these two secondary_boat_id should add up (that is 122) and to be added against the boat_id row in a measure.

*Taking the second row:

* boat_id is 2.

and because only one record matches, total_secondary_weight will be 22.

I have been trying to write correct dax for it for a while, and this is the dax for this measure i have written.

Added Weight Measure = SUMX(
'surfside_prod bullpens',
VAR filTable = FILTER(
'surfside_prod bullpens',
'surfside_prod bullpens'[secondary_boat_id] = EARLIER('surfside_prod bullpens'[boat_id])
)
RETURN sumX(SELECTCOLUMNS(filTable, "Secondary Weight", [secondary_boat_id]), [Secondary Weight] * 1) * 1
)

What am getting from this dax is this result. Not on each row, only the same. and I don't want sum, i want corresponding total_secondary_weight on each row.

I have been scratching my head around it for a while. Don't know where i'm going wrong. I'm new to this, please help me.

NOTE: I'm working with a MEASURE, and not a calculated column.

[Payeras_BI]

Hi @navinrangar ,

If I understood correctly try using ALLSELECTED instead of ALL in your measure.

[navinrangar]

Thanks it worked like charm!!

[navinrangar]

Hi @Payeras_BI,
Thankyou so much for the solution.

There is a few things that i want to add up.

Now, I have data based on dates.
And user is free to choose any date(s) from the slicer filter.

Now, the problem is that it is showing the same number no matter which date(s) i pick up.

For example, for boat_id = 2, i have 3 secondary_id entries.
one on 01-01-2023.

remaining two are on 02-01-2023.

But if i select, a date let us say, 01-01-2023, it should ideally show secondary_weight 22 for boat_id = 1.

But it shows the weight for all dates.

like this.

We are missing the date filter. Could you please modify the dax for this?
i need to filter based on dates as well as boat_id.

Thanks again!

[Payeras_BI · Accepted solution]

Hi @navinrangar ,

Maybe something like this?