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.
How to count in a table A, the associated values in a table B according to a condition and between two dates
Hi @Anonymous ,
Do you want to calculate the number of customers in the date range of tables A and B?Is there any relationship between these two tables?
Best Regards
Rena
Hi @v-yiruan-msft
Thank you very much for your contributions and help to the whole community.
What I really need is a union of my two publications yesterday, I will try to summarize:
As you know I have a goal table, it's called FO15-ANS Visita a clientes ents and another transactional table called FO15-Visita Cliente.
The goals change month by month so the goal table has a Fecha column where the date of the month (first day of the month) is loaded. This table is repeated for the following month and the Fecha column changes to the first day of the following month. In the transactional table there are all the visits and it has a selected date column FechaVis, so we create a selected FO15-Fechas table that helps us to relate both tables.
The ANS table has a selected Frecuencia column, which the sum of this column in month X gives us the total of the month's goal
Now, between both tables there is another relationship through a column called CodigoProyecto that is related by another selected FO15-DIM_Proyecto table.
This helps us compare what was done against the goal month by month using the filter options.
In order to carry out the control where the views carried out do not exceed the goal, we create a measure that helps us to count on Projects and individually assesses the data, this measure is called Performed Max. This measure is a conditional:
Realizadas Max = IF ( DISTINCTCOUNT('FO15-Visita Cliente'[prikey]) > sum('FO15-ANS Visita a clientes'[Frecuencia]) ; sum('FO15-ANS Visita a clientes'[Frecuencia]) ; [Realizadas Meta])
However, when we need the final data of all of the visits, it's showing me all of the visits, even those that exceed the goals..
So, what I need is a measure that helps me count only the visits made, but not does ones where the individual goal is exceede
I leave the model in PBIX.
https://1drv.ms/u/s!ArJzwBid6CN3xZtQwMskCbBah1SqJA?e=IiSVDn
Thank you very much for your help
Hi @Anonymous ,
Thanks for providing information, I'm checking it. Any progress I will update you in time. Thank you.
Best Regards
Rena
Hi @v-yiruan-msft, i just wanted to know if you've been able to work on a solution for me, if you need more info please let me know. Looking forward to hear from you.
Hi @Anonymous ,
Best Regards
Rena
Hi,
Ideally you should build a Calendar Table and create a relationship from the Date column of your base data table to the Date column of your Calendar Table. In the Calendar Table, write these calculated column formulas to extract Year and Month
Year = YEAR(Calendar[Date])
Month Name = FORMAT(Calendar[Date],"mmmm")
Month Number = MONTH(Calendar[Date])
Click on any cell in the Month Name column and go to Sort by Column > Month Number
To your visual, drag Year and Month from the Calendar Table and Vendedor from the base data table. Write this measure
Count = COUNTROWS(Data)
Hope this helps.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |