Forum Discussion
Filter a measure by two columns
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.
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] ) ) )
6 Replies
- LivioLanzo
Solution Sage
what is the logic behind the Nearest people amount? why is it 2 for Location1 and why is it 1 for Location 2 ?
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
- LivioLanzo
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] ) ) )
