Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
navinrangar
Advocate I
Advocate I

Dax Measure for Nested Row Contexts

Hi all,

I have a database table that looks like this.
 

navinrangar_1-1683267085113.png

 

 

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.



navinrangar_2-1683267530234.png

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.



 

1 ACCEPTED SOLUTION
Payeras_BI
Super User
Super User

Hi @navinrangar ,

Maybe something like this?

Payeras_BI_0-1683291659885.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

4 REPLIES 4
Payeras_BI
Super User
Super User

Hi @navinrangar ,

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

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Thanks it worked like charm!!

navinrangar
Advocate I
Advocate I

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.

navinrangar_0-1683609945477.png

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.

 

navinrangar_1-1683610095427.png

 

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.

navinrangar_2-1683610298359.png

 

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
Super User
Super User

Hi @navinrangar ,

Maybe something like this?

Payeras_BI_0-1683291659885.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors