Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am trying to use this to track progress for concrete installation. Each individual piece of concrete has it's own row in the table. I have found a workaround in case I can't find a solution here, but please bear with me as this will be a lengthy explanation.
I have a Column titled "CIP Concrete Install Status", with the 6 different possible values being: "Not Installed, 1) Formed, 2) Rebar Set, 3) Poured/Finished, 4) Cured, 5) Epoxy-ed/Complete.
There is a separate Column titled "Takeoff Quantity", which provides accurate measurement of each individual piece, based on what unit of measurement you are tracking it by.
There is a column for "Unit", which contains the possible values: "LF,SF,CY,EA". This column denotes what unit of measurement each individual piece of concrete's Takeoff Quantity is associated with.
I have been able to create individual calculated columns to determine the amount of each unit has been assigned to each status, but it is incredibly cumbersome and makes visualizing the data way more difficult.
First I created a calculated column for Linear Footage:
Again, this works fine and displays the information accurately, but the only way the unit of measure can be determined is by the column title.
I want to be able to have 1 column that displays All concrete that has been Poured/Finished, rather than creating 4 individual columns titled "CIP LF Poured/Finished", "CIP SqFt Poured/Finished", "CIP CY Poured/Finished", "CIP EA Poured/Finished
" and then repeating that for the other 5 statuses, thus having 20 calculated columns. There is no way that will be readable on a table.
I tried to Concantacate, but when i do that it won't sum the values anymore.
here is what i wrote, please tell me where i'm going wrong:
Solved! Go to Solution.
@MPRGray -
I hope someone comes up with a better solution. Creating a new table using GROUPBY ( CALCULATETABLE ( ) ) what I came up with:
Table = GROUPBY ( CALCULATETABLE ( TableName, TableName[CIP Concrete Install Status] = "3) Poured/Finished" ), TableName[Activity ID], TableName[Unit], "Total", SUMX ( CURRENTGROUP (), TableName[CIP Poured/Finished] ) )
https://dax.guide/calculatetable/
Proud to be a Super User!
@MPRGray -
Did you try https://www.sqlbi.com/articles/using-concatenatex-in-measures/? Providing sample data is a good idea.
Proud to be a Super User!
Thanks, I haven't tried that yet. I'm still pretty new to Power BI and have a bit of trouble with measures.
How would i reference a column like "CIP Install Status" in a measure?
Really not sure where to start with figuring this out. Sorry!
@MPRGray -
While you've taken the time to explain your data, I am still unsure of how your dataset looks. Please provide a sample dataset that we can try to assist with. You will have a better response from the community. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
https://www.dropbox.com/s/fkadl1b60xrkh4j/Sample%20CIP%20Data%20set.xlsx?dl=0
Okay this is a sample data set that i exported to excel.
Columns F-K in the attached workbook were made in Power Query.
the code for them is as follows:
= Table.AddColumn(#"Added Custom3", "CIP Not Installed", each if [CIP Concrete Install Status] = "Not Installed" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column", "CIP Formed", each if [CIP Concrete Install Status] = "1) Formed" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column4", "CIP Rebar Set", each if [CIP Concrete Install Status] = "2) Rebar Set" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column1", "CIP Poured/Finished", each if [CIP Concrete Install Status] = "3) Poured/Finished" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column2", "CIP Cured", each if [CIP Concrete Install Status] = "4) Cured" then [Takeoff Quantity] else null)
= Table.AddColumn(#"Added Conditional Column3", "CIP Epoxy-ed/Complete", each if [CIP Concrete Install Status] = "5) Epoxy-ed/Complete" then [Takeoff Quantity] else null)
I need a table showing Activity ID and the quantity of each unit for each installation status with both the correct amount and the Unit contained within the same cell.
Please let me know if this is not enough information.
Thanks,
@MPRGray -
I hope someone comes up with a better solution. Creating a new table using GROUPBY ( CALCULATETABLE ( ) ) what I came up with:
Table = GROUPBY ( CALCULATETABLE ( TableName, TableName[CIP Concrete Install Status] = "3) Poured/Finished" ), TableName[Activity ID], TableName[Unit], "Total", SUMX ( CURRENTGROUP (), TableName[CIP Poured/Finished] ) )
https://dax.guide/calculatetable/
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |