Forum Discussion

MattJessop's avatar
MattJessop
Frequent Visitor
7 years ago
Solved

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 togeth...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

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

     

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
MattJessop
Frequent Visitor
7 years ago

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's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

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

     

    • TomMartens's avatar
      TomMartens
      Icon for Super User rankSuper User
      7 years ago

      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's avatar
        LivioLanzo
        Icon for Solution Sage rankSolution Sage
        7 years ago

        Thanks TomMartens ! Yes TOPN sometimes is a hidden gem :)

    • MattJessop's avatar
      MattJessop
      Frequent Visitor
      7 years ago

      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.