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 Hou...
  • 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.

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