Ambiguity when relating tables

[Thiagops]

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)

- Customers (Related to outgoing invoice)

- Exit invoice

I'm using data tracking to choose the seller and the month, but I believe that because the customer table is not directly related to the calendar, it doesn't work. The result is not correct. I tried to relate the calendars table with the customers, but it says that it generates ambiguity with the outgoing invoice.

I'm using the following measure: 

New customer registration = DISTINCTCOUNTNOBLANK('CUSTOMER (WALLET)'[CLI_CODIGO])

[tamerj1]

Hi @Thiagops 

please provide screenshots of your data model (model view) and the visual that shows the wrong results 

[Thiagops]

Só funciona a seguimentação de dados para o vendedor.

A seguimentação de dados para ano e mes não funciona.

[tamerj1]

@Thiagops 
Please try

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

[Thiagops]

Still the same result

[Thiagops]

I also tried with USERELATIONSHIP, but it didn't work

[tamerj1]

@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]

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))

[tamerj1]

@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])

[Thiagops]

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.

[tamerj1 · Accepted solution]

@Thiagops 

You are right. Please try

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

[Thiagops]

@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]

@Thiagops 

No that was just a filter that simulates a relationship