Remove Duplicates Based on Values from Two Columns

[Anonymous]

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]

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]

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

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

[Anonymous]

@v-yulgu-msft

Thanks very much! That worked flawlessly!