topic Many-to-One Relationship Breaks Cumulative Total! in DAX Commands and Tips

Many-to-One Relationship Breaks Cumulative Total!

elocke — Mon, 08 Jun 2020 15:15:34 GMT

Need help getting a DAX cumulative total to work across two tables with a many-to-one relationship!

I have two very simple tables (A dates table, and a people table) connected by a "Name" column.

All I want to do is create a summarized table by date with the cumulative total of people, and a target value.

Here's the code for my DAX summarized table:

When the two tables have a 1-to-1 relationship, this code works perfectly!! Unfortunately, as soon as I have more than one record per name in my people table, and the relationship becomes a many-to-one relationship, the cumulative total stops counting cumulatively, and simply returns the total for that record.

Is there a way to get around this?? I've already tried using the "all" function, but that didn't help either.

Re: Many-to-One Relationship Breaks Cumulative Total!

elocke — Mon, 08 Jun 2020 15:35:56 GMT

Found the issue.

All I had to do was change the summarized table to the dates table instead of the people table. Send a kudo if you love the ever miniscule subtlties of Power BI table relationships and DAX. 🙂

Re: Many-to-One Relationship Breaks Cumulative Total!

Anonymous — Fri, 04 Aug 2023 12:10:44 GMT

this is most helpful 🙂 I struggled with this for way too long 😔