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.
I want to filter a calculated table by two different categories, to find the closest category based off the minimum distance.
The calculated table is:
How I want to pull the results together would look like this:
As this is in a calculated table pulled from my original table, I'd like to do this in a measure so that we can adjust a number of different variables.
I can get a measure working based off just one column, using CALCULATE however doing it by two causes issues, which I suspect is due to the MIN returning a lot of values when the measure is looking for a TRUE/FALSE return for each row.
Solved! Go to Solution.
Hello @MattJessop
Below is one solution to your scenario:
= COUNTROWS ( FILTER ( GENERATE ( VALUES ( Persons[Person] ), CALCULATETABLE ( TOPN ( 1, Data, Data[Distance], ASC ), ALL ( Locations ) ) ), Data[Location] IN VALUES ( Locations[Location] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
what is the logic behind the Nearest people amount? why is it 2 for Location1 and why is it 1 for Location 2 ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo - apologies as I thought I had replied to this earlier. This is because we want to treat each nearest person as unique, so they should only be counted against the category 1/location that has the lowest distance to:
It might make more sense to think of this with category 2 in the first column. It would need to look up for each person which Location is closest, and assign them to that. Category 1 is the first column in the data because this is the baseline category used for the measure. (i.e. Location 1 has X people).
Category 2 Category 1 Distance
James Location 1 3.34
James Location 2 4.88
Mark Location 1 4.53
Mark Location 2 1.24
Sue Location 1 5.33
Sue Location 2 6.77
Hello @MattJessop
Below is one solution to your scenario:
= COUNTROWS ( FILTER ( GENERATE ( VALUES ( Persons[Person] ), CALCULATETABLE ( TOPN ( 1, Data, Data[Distance], ASC ), ALL ( Locations ) ) ), Data[Location] IN VALUES ( Locations[Location] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for this! Sorry for late response, I've been on holiday last few weeks. Will need to have a play around with this but looks good to me.
Hey @LivioLanzo
a very smart solution, really like that. I have to admit that I use TOPN rarely, but this will change.
Cheers,
Tom
Thanks @TomMartens ! Yes TOPN sometimes is a hidden gem 🙂
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |