Forum Discussion

MPRGray's avatar
MPRGray
Advocate I
7 years ago
Solved

Combine Calculated numerical column with text formatted unit column

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, b...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    7 years ago

    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/groupby/

    https://dax.guide/calculatetable/

  • MPRGray's avatar
    MPRGray
    Advocate I
    7 years ago

    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's avatar
        MPRGray
        Advocate I
        7 years ago

        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,