Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Very good! Let's see if you can help me because I can't exactly square the calculations I want to do.
It turns out that I have a table of Visitors, where date (dd/mm/yyyy), country, city of origin and number of users are stored, such that:
Date | Country | City | NumUsuarios |
1/1/2021 | Spain | Madrid | 800 |
1/1/2021 | Spain | Barcelona | 100 |
1/1/2021 | Germany | Munich | 900 |
1/2/2021 | Spain | Madrid | 950 |
1/2/2021 | Spain | Barcelona | 150 |
1/2/2021 | Germany | Munich | 150 |
1/3/2021 | Spain | Madrid | 700 |
1/3/2021 | Spain | Barcelona | 800 |
1/3/2021 | Germany | Munich | 1200 |
.... | ... | ... | ... |
I have some filters that allow you to select Country, City, Month and Year, with the peculiarity that if you select the month of March, the calculations are cumulative from January to March. That is, if I have to take out the number of cities that meet the filter of Year=2021 and Month=2 and that have more than 100 users, the result should be "Cities=2" because Madrid and Munich add up to more than 100 users between the months of January and February. If it were the filter of Year=2021 and Month=3 and that they have more than 1000 users, the result would be 3 because already Madrid, Barcelona and Munich exceed the 1000 users enese accumulated from months of January to March.
Be that as it may, I can't square that end result of cities. With the 100 tests I've done, I'm left with the cities of the selected month, instead of the cumulative one, for example. I've tried to make a table with SUMMARIZE where only the data that meets those filters is put in.... in short, always hitting me against a wall.
From so much doing and undoing, in the end I've been left with something like this:
Ciudades =
var anho = SELECTEDVALUE(Calendar[Fecha].[ Año])
var mes = SELECTEDVALUE('Calendar'[Fecha].[ NroMes])
var fecha = DATESBETWEEN('Calendar'[Fecha], DATE(anho, 1, 1), DATE(anho, mes, 1))
VAR actual = FILTER(
ALLEXCEPT('Visitantes', 'Calendar'[Fecha].[ Mes], 'Visitantes'[Pais], 'Visitantes'[Ciudad]),
'Pasajeros por Escala'[Fecha]>=FIRSTDATE(fecha) && 'Pasajeros por Escala'[Fecha]<=LASTDATE(fecha) &&
SUM('Pasajeros por Escala'[NumUsuarios])>1000
)
var valor = calculate(DISTINCTCOUNT('Visitantes'[Ciudad]),actual)
RETURN valor
Let's see if you can give me some light.
Thank you very much in advance.
Solved! Go to Solution.
In the end I found the solution:
Ciudades =
var anho = SELECTEDVALUE(Calendar[Fecha].[ Año])
var mes = SELECTEDVALUE('Calendar'[Fecha].[ NroMes])
var fecha = DATESBETWEEN('Calendar'[Fecha], DATE(anho, 1, 1), DATE(anho, mes, 1))
VAR actual = FILTER(
ALLEXCEPT('Visitantes', Calendar[Fecha].[ Año],'Visitantes'[País]),
'Visitantes'[Fecha]>=FIRSTDATE(fecha) && 'Visitantes'[Fecha]<=LASTDATE(fecha)
)
var tablaResumida= CALCULATETABLE(
SUMMARIZE('Visitantes', 'Visitantes'[Ciudad], 'Visitantes'[País], "NumUsuarios", SUM('Visitantes'[Pasajeros])),
actual)
RETURN calculate(DISTINCTCOUNT('Visitantes'[Ciudad]),FILTER(tablaResumida, [NumUsuarios]>1000))
In the end I found the solution:
Ciudades =
var anho = SELECTEDVALUE(Calendar[Fecha].[ Año])
var mes = SELECTEDVALUE('Calendar'[Fecha].[ NroMes])
var fecha = DATESBETWEEN('Calendar'[Fecha], DATE(anho, 1, 1), DATE(anho, mes, 1))
VAR actual = FILTER(
ALLEXCEPT('Visitantes', Calendar[Fecha].[ Año],'Visitantes'[País]),
'Visitantes'[Fecha]>=FIRSTDATE(fecha) && 'Visitantes'[Fecha]<=LASTDATE(fecha)
)
var tablaResumida= CALCULATETABLE(
SUMMARIZE('Visitantes', 'Visitantes'[Ciudad], 'Visitantes'[País], "NumUsuarios", SUM('Visitantes'[Pasajeros])),
actual)
RETURN calculate(DISTINCTCOUNT('Visitantes'[Ciudad]),FILTER(tablaResumida, [NumUsuarios]>1000))
@Syndicate_Admin , Based on what I got, Try a measure like
countx(filter(summarize(Table, Table[Country], Table[City], "_1", sum(Table[NumUsuarios])), [_1]>1000),[City])
Unfortunately it only takes into account the value marked in the MES filter. In the case of the example, I would return Cities=1 because Munich is the only one in March that meets that it is greater than 1000, when it should be Cities=3 because the sum of January ,February and March of the three cities exceed 1000 users.
The value of the month filter seems to prevail, and does not accumulate them.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |