Adding one column by removing duplicates in another

[Syndicate_Admin]

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 · Accepted solution]

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.

[Bibiano_Geraldo]

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 ])