topic Re: Ambiguity when relating tables in DAX Commands and Tips

Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 14:02:01 GMT

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

Re: Ambiguity when relating tables

tamerj1 — Fri, 17 Feb 2023 14:21:48 GMT

Hi Thiagops

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

Re: Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 14:28:26 GMT

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

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

Re: Ambiguity when relating tables

tamerj1 — Fri, 17 Feb 2023 15:15:00 GMT

Thiagops
Please try

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

Re: Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 15:55:33 GMT

Still the same result

Re: Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 15:56:55 GMT

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

Re: Ambiguity when relating tables

tamerj1 — Fri, 17 Feb 2023 16:29:44 GMT

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

Re: Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 17:28:29 GMT

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

Re: Ambiguity when relating tables

tamerj1 — Fri, 17 Feb 2023 17:38:31 GMT

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

Re: Ambiguity when relating tables

Thiagops — Fri, 17 Feb 2023 18:51:23 GMT

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.

Re: Ambiguity when relating tables

tamerj1 — Sat, 18 Feb 2023 04:57:18 GMT

Thiagops

You are right. Please try

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

Re: Ambiguity when relating tables

Thiagops — Sun, 19 Feb 2023 13:21:55 GMT

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

Does this that was done force a relationship?

Re: Ambiguity when relating tables

tamerj1 — Sun, 19 Feb 2023 14:30:45 GMT

Thiagops

No that was just a filter that simulates a relationship