Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Remove Duplicates Based on Values from Two Columns

Hey guys, I'm have here a table with costs and two cost centers. The problem is, some of these costs are tied to both cost centers, so I end up with duplicated costs, which sums up and gives me a totally wrong sum.

 

 

Example (trip number is unique):

 

 

Cost Center - Trip Number - Cost

    100         -      02           -  100

    200         -      33           -  150

    200         -      02           - 150

 

What I need to do is, find out which trip numbers are tied to both cost centers, and remove those rows from only one cost center.

 

Can anyone help me with that? Thanks!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Above formula is DAX which should be used in report building view rather tha Query Editor. You used semicolon (;) after ALLEXCEPT('Actuals' ; , please change it with comma (,).

     

    If it still doesnt't work, please replace all comma (,) with semicolon (;) in above formula for a test.

    Is tied to multipe centers =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Cost TB'[Cost Center] );
        ALLEXCEPT ( 'Cost TB';'Cost TB'[Trip Number] )
    )
        > 1;
    "Y";
    "N"
)

     

    Regards,

    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    To check whether a trip number is tied to both cost centers, you can create such a column:

    Is tied to multipe centers =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Cost TB'[Cost Center] ),
        ALLEXCEPT ( 'Cost TB', 'Cost TB'[Trip Number] )
    )
        > 1,
    "Y",
    "N"
)

     

    Then, you can filter out those rows where [Is tied to multiple centers] equals "Y" in visual or use a FILTER function to create a calculated table.

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      hi v-yulgu-msft

       

      Thanks very much for the help. I was trying to use your formula here, but all I'm getting is an error statement: "Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly." If I try to use it on the report building view, I also get an error:

       

       

       

       

      Do you have any idea on how to make it work? Thanks!

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

        Above formula is DAX which should be used in report building view rather tha Query Editor. You used semicolon (;) after ALLEXCEPT('Actuals' ; , please change it with comma (,).

         

        If it still doesnt't work, please replace all comma (,) with semicolon (;) in above formula for a test.

        Is tied to multipe centers =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Cost TB'[Cost Center] );
        ALLEXCEPT ( 'Cost TB';'Cost TB'[Trip Number] )
    )
        > 1;
    "Y";
    "N"
)

         

        Regards,

        Yuliana Gu