Filter a measure by two columns

[MattJessop]

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.

[LivioLanzo]

@MattJessop

what is the logic behind the Nearest people amount? why is it 2 for Location1 and why is it 1 for Location 2 ?

[MattJessop]

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 · Accepted 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] )
    )
)

[MattJessop]

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.

[TomMartens]

Hey @LivioLanzo

a very smart solution, really like that. I have to admit that I use TOPN rarely, but this will change.

Cheers,

Tom

[LivioLanzo]

Thanks @TomMartens ! Yes TOPN sometimes is a hidden gem 🙂