Forum Discussion
Column
Dear Masters,
I need your advice regarding formula below :
)
I would like to calculate new column named "TPH" but i can't (not appears) grap column "RunningHrs","Bucket".
Please your advice.
cheers 🙏🙏
Hi Snonsup87
The issue occurs because ADDCOLUMNS cannot reference "virtual" columns (like Bucket and RunningHrs) within the same step they are created. To fix this, you must nest the functions so the outer layer can see the columns generated by the inner layer.
Additionally, you should remove the quotation marks from the column names inside the DIVIDE function, as DAX treats quoted text as strings rather than column references.
Recommended DAX Solution
קטע קוד
EVALUATE
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
Equipment,
Equipment[Shift Date],
Equipment[Shift Code],
Equipment[ID Operator],
Equipment[Operator Name],
Equipment[Month],
Equipment[Year]
),
"Bucket",
VAR ActualMucking =
CALCULATE(
SUM(BucketbyOperator[Actual Mucking]),
FILTER(
ALL(BucketbyOperator),
BucketbyOperator[Date] = Equipment[Shift Date] &&
BucketbyOperator[Shift] = Equipment[Shift Code] &&
BucketbyOperator[ID Operator] = Equipment[ID Operator]
)
)
RETURN COALESCE(ActualMucking, 0),
"RunningHrs",
CALCULATE(
SUM(Equipment[Duration (Hour)]),
Equipment[Status] = "Ready"
)
),
"TPH",
DIVIDE([Bucket], [RunningHrs], 0)
)
Key Improvements:
Layering: By wrapping the first ADDCOLUMNS in a second one, the TPH calculation now recognizes [Bucket] and [RunningHrs] as valid fields.Performance: Using a variable (VAR) for the Bucket calculation prevents the engine from running the same complex filter twice.
Syntax Correction: Removed the double quotes from DIVIDE("RunningHrs","Bucket") and changed it to DIVIDE([Bucket], [RunningHrs]).
COALESCE: Replaced the IF(...=BLANK(), 0, ...) logic with COALESCE, which is the standard, high-performance way to handle nulls in DAX.
If the logic above does not produce the expected results due to specific relationships in your data model, please upload a sample file (Excel or .PBIX) to a cloud drive (Google Drive, OneDrive, etc.) with a clear example of your desired output and share the link here.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Hi Snonsup87
unfortunately we cannot help based on your message.
Please follow this instructions:
Include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Hi Snonsup87
The issue occurs because ADDCOLUMNS cannot reference "virtual" columns (like Bucket and RunningHrs) within the same step they are created. To fix this, you must nest the functions so the outer layer can see the columns generated by the inner layer.
Additionally, you should remove the quotation marks from the column names inside the DIVIDE function, as DAX treats quoted text as strings rather than column references.
Recommended DAX Solution
קטע קוד
EVALUATE
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
Equipment,
Equipment[Shift Date],
Equipment[Shift Code],
Equipment[ID Operator],
Equipment[Operator Name],
Equipment[Month],
Equipment[Year]
),
"Bucket",
VAR ActualMucking =
CALCULATE(
SUM(BucketbyOperator[Actual Mucking]),
FILTER(
ALL(BucketbyOperator),
BucketbyOperator[Date] = Equipment[Shift Date] &&
BucketbyOperator[Shift] = Equipment[Shift Code] &&
BucketbyOperator[ID Operator] = Equipment[ID Operator]
)
)
RETURN COALESCE(ActualMucking, 0),
"RunningHrs",
CALCULATE(
SUM(Equipment[Duration (Hour)]),
Equipment[Status] = "Ready"
)
),
"TPH",
DIVIDE([Bucket], [RunningHrs], 0)
)
Key Improvements:
Layering: By wrapping the first ADDCOLUMNS in a second one, the TPH calculation now recognizes [Bucket] and [RunningHrs] as valid fields.Performance: Using a variable (VAR) for the Bucket calculation prevents the engine from running the same complex filter twice.
Syntax Correction: Removed the double quotes from DIVIDE("RunningHrs","Bucket") and changed it to DIVIDE([Bucket], [RunningHrs]).
COALESCE: Replaced the IF(...=BLANK(), 0, ...) logic with COALESCE, which is the standard, high-performance way to handle nulls in DAX.
If the logic above does not produce the expected results due to specific relationships in your data model, please upload a sample file (Excel or .PBIX) to a cloud drive (Google Drive, OneDrive, etc.) with a clear example of your desired output and share the link here.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Snonsup87,
Thank you for reaching out to Microsoft Fabric Community.
Thank you FBergamaschi and Ritaf1983 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @Snonsup87,
We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
