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

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
Icon for Microsoft Employee rankMicrosoft 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    v-yulgu-msft

    Thanks very much! That worked flawlessly!