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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-yulgu-msft

      Thanks very much! That worked flawlessly!