topic Re: Running Decrement based on two values from 3 related tables. in DAX Commands and Tips

Running Decrement based on two values from 3 related tables.

Gabe_V1 — Fri, 09 Aug 2024 20:06:57 GMT

In Power BI I have a table-style visualization which uses a field called "AssignmentWork" from a table caled "AssignmentTimephasedDataSet", a field called "Capacity" and a field called "UtilizationPercentage" from a table called "ResourceTimephasedDataSet", and a sortation field called "Stack Rank (Raw)" from a table called "Projects". Currently the "Capacity" and "UtilizationPercentage" columns are just static columns. However, I would like the "Capacity" column in the visual (which we'll call "Running Capacity") to decrement by "AssignmentWork", and "UtilizationPercentage" to increase by "AssignmentWork". The starting value of the new "Running Capacity" column should be the "Capacity" value corresponding to the lowest shown value in the "Stack Rank (Raw)" column in the visual (noting that rows may be filtered out of the visual, but the "Stack Rank (Raw)" column values will always be in ascending order, as per the example below)

I made a stab at a DAX calculated column for running decremented capacity (modified from some other code I found in another forum), but I'm a DAX newbie, and when I run it, it doesn't actually return anything, and I am having a hard time figuring out how to make it work given the relationships of the tables needed. From other articles I've read, there may be a better way to do this as a measure instead of a calculated column, but I'm not sure how to make it work.

I sincerely appreciate any help you can provide! Thanks!

The source for these columns is:

Total Demand: Sum of 'AssignmentTimephasedDataSet'[AssignmentWork]
Capacity: Sum of 'ResourceTimephasedDataSet'[Capacity]
Utilization: 'ResourceTimephasedDataSet'[UtilizationPercentage]
Stack Rank: Sum of 'Projects'[Stack Rank (Raw)]

These tables are related in the data model as follows:

Projects->1:M->AssignmentTimephasedDataSet on ProjectUID
ResourceTimephasedDataSet is related to AssignmentTimephasedDataSet by way of a Resources table intermediary as follows:
ResourceTimephasedDataSet->M:1->Resources on ResourceUID
Resources->1:M->AssignmentTimephasedDataSet on ResourceUID

Visual Representation of the table relationships:

This is the code I have so far:

Running Capacity = VAR _SelResUID = SELECTEDVALUE(AssignmentTimephasedDataSet[ResourceUID]) VAR _SelStackRank = SELECTEDVALUE('Projects'[Stack Rank (Raw)]) VAR _CapQty = SELECTCOLUMNS(FILTER('ResourceTimephasedDataSet', 'ResourceTimephasedDataSet'[ResourceUID] = _SelResUID), "@ResourceTimephasedDataSet", 'ResourceTimephasedDataSet'[Capacity]) VAR _SumQty = CALCULATE( SUM(AssignmentTimephasedDataSet[AssignmentWork]), REMOVEFILTERS(), SUMMARIZE(AssignmentTimephasedDataSet, AssignmentTimephasedDataSet[ResourceUID]), 'Projects'[Stack Rank (Raw)] <= _SelStackRank) RETURN _CapQty - _SumQty

This is my current outcome:

This is my desired outcome

Re: Running Decrement based on two values from 3 related tables.

SachinNandanwar — Sat, 03 Aug 2024 06:45:29 GMT

A short in dark

Running Capacity = VAR _CurrentStackRank = SELECTEDVALUE('Projects'[Stack Rank (Raw)]) VAR _SelResUID= SELECTEDVALUE(AssignmentTimephasedDataSet[ResourceUID]) VAR _CapQty = CALCULATE( SUM('ResourceTimephasedDataSet'[Capacity]), FILTER( ALL('ResourceTimephasedDataSet'), 'ResourceTimephasedDataSet'[ResourceUID] = _SelResUID ) VAR _StackRank = OFFSET( -1, ALLSELECTED('Projects'), ORDERBY('Projects'[Stack Rank (Raw)], ASC) ) VAR _SumQty = CALCULATE( SUM(Projects[TotalDemand]), FILTER(ALL(Projects),'Projects'[Stack Rank (Raw)] <= _StackRank) ) RETURN _CapQty - _SumQty

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Mon, 05 Aug 2024 14:36:52 GMT

Hi SachinNandanwar, Thank you for your help. I think it is closer, just by looking at it. I changed the SUM field reference in the _SumQty variable from Projects[TotalDemand] to AssignmentTimephasedDataSet[AssignmentWork] because "TotalDemand" is just the front end name on the report, and actually refers to AssignmentTimephasedDataSet[AssignmentWork]. However, now I get this error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Any thoughts?

Re: Running Decrement based on two values from 3 related tables.

SachinNandanwar — Tue, 06 Aug 2024 07:52:40 GMT

Please share some sample data or the PBI file.

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Tue, 06 Aug 2024 19:03:04 GMT

Hi SachinNandanwar, The sterilized data is in the screenshot above under where it says, "My current and desired outcome". Unfortunately, I can't share the PBI file directly. There was no change in what shows up in that data dump between my original post and when I made the updates you suggested to "Running Capacity"

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Tue, 06 Aug 2024 21:33:42 GMT

Hi, Gabe_V1

Try Below measures

Measure = var a = SUMX(FILTER(ALL('Table'[Stack Rank],'Table'[Capacity]),'Table'[Stack Rank]=1),'Table'[Capacity]) var b = MAXX(FILTER(ALL('Table'[Stack Rank]),'Table'[Stack Rank]<MIN('Table'[Stack Rank])),[Stack Rank]) var c = SUMX(FILTER(ALL('Table'[Stack Rank],'Table'[Total Demand]),'Table'[Stack Rank]<MIN('Table'[Stack Rank])),'Table'[Total Demand]) RETURN a-c

% Measure

%_Measure = DIVIDE(SUM('Table'[Total Demand]),SUMX('Table',[Measure]))

Best regards,
Dangar

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Tue, 06 Aug 2024 23:19:43 GMT

Hi Dangar,

Unfortunately your solution doesn't take into account that the Stack Rank (Raw), Capacity and Total Demand fields all come from different (but related) tables and the ALL function only allows fields from the same table. Modifying your formula to include the actual table names as noted above, this is what it looks like:

Running Capacity = var a = SUMX(FILTER(ALL('Projects'[Stack Rank (Raw)],'ResourceTimephasedDataSet'[Capacity]),'Projects'[Stack Rank (Raw)]=1),'ResourceTimephasedDataSet'[Capacity]) var b = MAXX(FILTER(ALL('Projects'[Stack Rank (Raw)]),'Projects'[Stack Rank (Raw)]

The error message thrown is:

"All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table."

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Thu, 08 Aug 2024 15:40:47 GMT

Hi Dangar332,

It seems like we're so close! I tried merging the Capacity and Stack Rank fields all into the AssignmentTimephasedDateSet table, but its so huge that I can't even get to the point of being able to do field grouping to get it down to a manageable number of rows before it times out. Do you have a way of modifying your measure to allow for the fields to be in the different tables, instead of 1?

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Thu, 08 Aug 2024 17:05:36 GMT

Hi, Gabe_V1

Can you provide some sample data it would be great.

Best regards,

Dangar

If this post helps then please consider Accept it as the solution to help the other members find it more quickly

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Thu, 08 Aug 2024 19:04:23 GMT

Hi, Gabe_V1

You can try below measure

MEASURE = VAR a = CALCULATE ( SUM ( 'ResourceTimephasedDataSet'[Capacity] ), 'Projects'[Stack Rank (Raw)] = 1, REMOVEFILTERS () ) VAR b = MAXX ( FILTER ( ALL ( 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] < 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] ) VAR c = CALCULATE ( SUM ( 'Yourtablename'[Total Demand] ), FILTER ( ALL ( 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] < MIN ( 'Projects'[Stack Rank (Raw)] ) ) ) RETURN a - c

%_Measure = DIVIDE ( SUM ( 'Yourtablename'[Total Demand] ), SUMX ( ALL ( 'Projects'[Stack Rank (Raw)] ), [Measure] ) )

Best regards,

Dangar

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Fri, 09 Aug 2024 15:19:04 GMT

Hi Dangar332 Can you clarify specifically what sample data you need? I provided screenshots of the output in the original post. Do you need that in text format? Data scraped from the underlying related tables? I'll do what I can to help.

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Fri, 09 Aug 2024 18:04:54 GMT

Hi, Gabe_V1

Form your screen short it's not useful to generate output you want.
it would be better if you proivde data Table wise and Relationship-column from which Tables are connected .

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Fri, 09 Aug 2024 19:30:18 GMT

Hi Dangar332 I think I see what you're getting at, and I noticed that I left off a crucial piece of information: the field, which is called "Total Demand" in the visual, is actually a field called "AssignmentWork" on the AssignmentTimephasedDataSet. Sorry for any confusion that caused. I'll see if I can update the original post as well.

The Projects table, which contains the "Stack Rank (Raw)" field has a primary Key, ProjectUID, which is related to the AssignmentTimephasedDataSet table, which has the AssignmentWork field, in a 1:Many relationship on ProjectUID (bi-directional):

The ResourceTimephasedDataSet, which has the Capacity and UtilizationPercentage fields, also has a Foreign Key called ResourceUID, and is related to the intermediary Resources table in a Many:1 relationship on ResourceUID (one-directional):

From there, Resources is related to AssignmentTimephasedDataSet on its Primary Key, ResourceUID in a 1:Many relationship (bi-directional):

This is what the output of the these tables and relationships looks like in Power BI (note: I reset the column names so they match the table field names for clarity):

I hope this helps!

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Fri, 09 Aug 2024 20:04:37 GMT

Hi, Gabe_V1

Did you try my yesterday's Measure.
I had make that measure after seeing your semantic model and it might work for you.

just do one thing in that measure
in that measure just replace sum('Yourtablename'[totaldemand]) with sum(assignmentTimephasedDataSet'[AssignmentWork]).

MEASURE = VAR a = CALCULATE ( SUM ( 'ResourceTimephasedDataSet'[Capacity] ), 'Projects'[Stack Rank (Raw)] = 1, REMOVEFILTERS () ) VAR b = MAXX ( FILTER ( ALL ( 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] < 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] ) VAR c = CALCULATE ( sum('assignmentTimephasedDataSet'[AssignmentWork]), FILTER ( ALL ( 'Projects'[Stack Rank (Raw)] ), 'Projects'[Stack Rank (Raw)] < MIN ( 'Projects'[Stack Rank (Raw)] ) ) ) RETURN a - c

Let me Know that part work or not?

Best regards,

Dangar

If this post helps then please consider Accept it as the solution to help the other members find it more quickly

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Fri, 09 Aug 2024 20:12:56 GMT

Hi Dangar332 I tried out what you sent. It looks like its just copying down the first value:

Re: Running Decrement based on two values from 3 related tables.

Dangar332 — Fri, 09 Aug 2024 20:40:54 GMT

Hi, Gabe_V1

try below measure

MEASURE = VAR a = CALCULATE ( SUM ( 'ResourceTimephasedDataSet'[Capacity] ), 'Projects'[Stack Rank (Raw)] = 1, REMOVEFILTERS () ) var b = CALCULATE( max('Projects'[Stack Rank (Raw)]), 'Projects'[Stack Rank (Raw)]< max('Projects'[Stack Rank (Raw)]) ) VAR c = CALCULATE ( sum('assignmentTimephasedDataSet'[AssignmentWork]), 'Projects'[Stack Rank (Raw)]<b ) RETURN a - c

If it not work then
Could you please provide some sample data along with the tables you mentioned above?

Re: Running Decrement based on two values from 3 related tables.

Gabe_V1 — Fri, 09 Aug 2024 22:51:54 GMT

Hi Dangar332 That didn't work either, sadly. It was the same result as the previous; repeated values.

I can't send the full Power BI file because of privacy stuff, but I did a data dump of the relevant columns of the tables with as many rows as I could export (Power BI maxes out at 1000 rows) and put them in an Excel file, and made the data model mimic what is in my Power BI file. Here is the link: https://docs.google.com/spreadsheets/d/12_LiG4hT-e3cq5AlsaUWzxzKUK2zZcDY/edit?usp=sharing&ouid=102253249084355900549&rtpof=true&sd=true

I hope this helps, and thank you for all your efforts.

Re: Running Decrement based on two values from 3 related tables.

Anonymous — Mon, 09 Sep 2024 08:39:12 GMT

Hi Gabe_V1

The data you provided cannot be tested, and when I create the PBIX file and connect the relationships, I find that there is no match between the ProjectUID in the Project and the ProjectUID in the AssignmentTimephasedDataSet table.

If possible, please simplify your data and make sure it's correct.

It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.