Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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])
Solved! Go to Solution.
You are right. Please try
Cadastro de NOVOS clientes2 =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'CLIENTE (CARTEIRA)'[CLI_CODIGO] ),
'CLIENTE (CARTEIRA)'[Registration Date] IN VALUES ( CALENDARIO[Date] )
)
Hi @Thiagops
please provide screenshots of your data model (model view) and the visual that shows the wrong results
Só funciona a seguimentação de dados para o vendedor.
A seguimentação de dados para ano e mes não funciona.
@Thiagops
Please try
New customer registration =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'CUSTOMER (WALLET)'[CLI_CODIGO] ),
CROSSFILTER ( 'Calendar'[Date], 'Exit invoice'[Date], BOTH )
)
Still the same result
I also tried with USERELATIONSHIP, but it didn't work
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 )
)
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))
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])
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.
You are right. Please try
Cadastro de NOVOS clientes2 =
CALCULATE (
DISTINCTCOUNTNOBLANK ( 'CLIENTE (CARTEIRA)'[CLI_CODIGO] ),
'CLIENTE (CARTEIRA)'[Registration Date] IN VALUES ( CALENDARIO[Date] )
)
@tamerj1 thank you very much.
It was right. GOD reward you, you helped me a lot.
Does this that was done force a relationship?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
125 | |
32 | |
27 | |
24 | |
23 |