Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

Adding one column by removing duplicates in another

I'm working on a model to get a workshop productivity report. Let's see if you can help me create a measure:

I have a table d_HorasCalendario with the following columns: Date, Workshop, Calendar Hours, OperatorID.

I would like to get the monthly hours per workshop, but when I do it with SUM (Calendar Hours = SUM(d_HorasCalendario[Calendar Hours])), I add up the hours of all the operators in the workshop.

I want to take the hours per workshop without taking into account the operators, i.e. removing duplicates in the Workshop column. How could I do that with a measure? Attached is an image of the table.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Syndicate_Admin ,

    Please try to create a measure with below dax formula:

    Measure =
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _year =
    YEAR ( _date )
VAR _month =
    MONTH ( _date )
VAR _ws =
    SELECTEDVALUE ( 'Table'[Workshop] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Workshop] = _ws
            && YEAR ( [Date] ) = _year
            && MONTH ( [Date] ) = _month
    )
RETURN
    SUMX ( tmp, [Calendar Hour] )

    Add a table visual with table fields and measure:

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    2 years ago
    Hi, try the following steps
    1- Create a new calculated column in your table with the following code:

    FirstValueOrZero =
    VAR FirstValue = CALCULATE(MIN(Your_Table_Name[Horas Calendario]), FILTER(Your_Table_Name, Your_Table_Name[Fecha] = EARLIER(Your_Table_Name[Fecha])))
    RETURN
        IF(Your_Table_Name[Horas Calendario] = FirstValue, FirstValue, 0)
     
    2- Create a new measure with the following code:
    Measure = SUM(Your_Table_Name[FirstValueOrZero ])
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin ,

    Please try to create a measure with below dax formula:

    Measure =
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _year =
    YEAR ( _date )
VAR _month =
    MONTH ( _date )
VAR _ws =
    SELECTEDVALUE ( 'Table'[Workshop] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Workshop] = _ws
            && YEAR ( [Date] ) = _year
            && MONTH ( [Date] ) = _month
    )
RETURN
    SUMX ( tmp, [Calendar Hour] )

    Add a table visual with table fields and measure:

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.