Running Decrement based on two values from 3 related tables.

[Gabe_V1]

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

[Anonymous]

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.

[Dangar332]

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.

[Gabe_V1]

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?

[Dangar332]

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.

[Dangar332]

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

[Gabe_V1]

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.

[Dangar332]

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 .

[Gabe_V1]

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!

[Dangar332]

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

[Gabe_V1]

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

[Dangar332]

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?  

[Gabe_V1]

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=10225...

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

[Gabe_V1]

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."

[SachinNandanwar]

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

[Gabe_V1]

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?

[SachinNandanwar]

Please share some sample data or the PBI file.

[Gabe_V1]

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"