Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated column returning wrong value

Hey, im having problem with a calculated column. I have a column with ID CLIENTE (more than 50K lines) including repeated values and I need to do a new column that works with a kind of COUNTIF (retur...
Need Help
Tutorial Requests
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    flag cliente =
DIVIDE (
    1,
    CALCULATE (
        COUNTROWS ( BASE_BANCO2_20221223 ),
        ALLEXCEPT ( BASE_BANCO2_20221223, BASE_BANCO2_20221223[ID CLIENTE] )
    ),
    0
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

flag cliente =
DIVIDE (
    1,
    CALCULATE (
        COUNTROWS ( BASE_BANCO2_20221223 ),
        ALLEXCEPT ( BASE_BANCO2_20221223, BASE_BANCO2_20221223[ID CLIENTE] )
    ),
    0
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

It worked!! but why can you explain what's the point of using countrows and using allexcept? 

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    COUNTROWS is just a bit more optimised I think, so I tend to use that rather than COUNT.

    The real key is the ALLEXCEPT. When you are creating a calculated column every column in the table is part of the filter, ALLEXCEPT removes the extra columns and you can just keep the columns that you need to be in the filter. This also helps to avoid circular dependency errors if you add multiple calculated columns to the same table.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for the solution and explanation!!