Forum Discussion

Thiagops's avatar
Thiagops
Icon for Helper II rankHelper II
3 years ago
Solved

Ambiguity when relating tables

Good morning, I need to know how many customers a seller registered in the month. I have the following tables: - Calendar (Related to outgoing invoice) - Sellers (related to customers) - Cust...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Thiagops 

    You are right. Please try

    Cadastro de NOVOS clientes2 =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'CLIENTE (CARTEIRA)'[CLI_CODIGO] ),
    'CLIENTE (CARTEIRA)'[Registration Date] IN VALUES ( CALENDARIO[Date] )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Thiagops 

Apologies, that was a mistake. Please try

New customer registration =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'CUSTOMER (WALLET)'[CLI_CODIGO] ),
CROSSFILTER ( Customer[Customer Key], 'Exit invoice'[Customer Key], BOTH )
)

Thiagops's avatar
Thiagops
Icon for Helper II rankHelper II
3 years ago

The result has changed as the data slices change, but still the values ​​are wrong.
The tables CLIENTE (CARTEIRA) and NF_SAIDA (CARTEIRA) are related by column CLI_CODIGO does it interfere?
The code I used now was:

Cadastro de NOVOS clientes2 =
CALCULATE(
DISTINCTCOUNTNOBLANK('CLIENTE (CARTEIRA)'[CLI_CODIGO]),
CROSSFILTER('CLIENTE (CARTEIRA)'[CLI_CODIGO],'NF_SAIDA (CARTEIRA)'[CLI_CODIGO],Both))

  • Thiagops's avatar
    Thiagops
    Icon for Helper II rankHelper II
    3 years ago

    I counted directly in the table and the result should be 10 (If you filter seller X in December 2022, he registered only 10 customers). The formulas mentioned above are returning the number of customers he sold (In the case of December, it returns 31).

    From what I understand this happens because the CALENDARIO table is filtered by the NF_SAIDA (WALLET) table. If we can find some way to relate the CALENDAR with the CLIENT (WALLET) directly, I think that solves it.

     

  • Thiagops's avatar
    Thiagops
    Icon for Helper II rankHelper II
    3 years ago

    tamerj1  thank you very much.
    It was right. GOD reward you, you helped me a lot.

    Does this that was done force a relationship?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Thiagops 

    No that was just a filter that simulates a relationship 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Thiagops 

    How did you judge that the results are wrong? However, you can simply return the same result as

    Cadastro de NOVOS clientes2 =
    DISTINCTCOUNTNOBLANK('NF_SAIDA (CARTEIRA)'[CLI_CODIGO])

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Thiagops 

    You are right. Please try

    Cadastro de NOVOS clientes2 =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'CLIENTE (CARTEIRA)'[CLI_CODIGO] ),
    'CLIENTE (CARTEIRA)'[Registration Date] IN VALUES ( CALENDARIO[Date] )
)