Forum Discussion

kbandito's avatar
kbandito
Frequent Visitor
5 years ago
Solved

DAX to count based on another column

Hi, I am looking for a DAX to count the number of brands available in BBB when I selected AAA.

I need a DAX because i need to build charts that can breakdown the count by category.

 

Table 1: Location

Main locationWithin 3km
AAABBB
AAABBB
BBBCCC

 

Table 2: POI

LocationPOICategory
BBBLVLuxury
BBBGAPFashion
BBBKidsKids
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi kbandito ,

     

    You can try to create a measure and put it into a card. When you select AAA, the card returns the number of brands 3 km away from you.

    Count =
CALCULATE (
    COUNTROWS ( 'POI' ),
    FILTER (
        'POI',
        [Location]
            = CALCULATE (
                MAX ( 'Location'[Within 3km] ),
                FILTER (
                    'Location',
                    [Main location] = SELECTEDVALUE ( Location[Main location] )
                )
            )
    )
)

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    kbandito - Maybe:

    Measure =
  VAR __Search = MAX(Location[Within 3km])
RETURN
  COUNTROWS(FILTER('POI',[Location]=__Search))

Or

Measure =
  VAR __Search = MAX(Location[Within 3km])
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('POI',[Location]=__Search),"brands",[Category])))

     

    Otherwise, please provide additional details.

    • kbandito's avatar
      kbandito
      Frequent Visitor
      5 years ago

      Hi, the first measures kinda works, but i think the MAX function limits it to show only 1 location.

      For example, AAA will have 5 locations in the Within 3km list. Now the measure is only showing the POI count for just 1 location, i will need it to show POI count for all 5 locations when AAA is selected.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi kbandito ,

     

    You can try to create a measure and put it into a card. When you select AAA, the card returns the number of brands 3 km away from you.

    Count =
CALCULATE (
    COUNTROWS ( 'POI' ),
    FILTER (
        'POI',
        [Location]
            = CALCULATE (
                MAX ( 'Location'[Within 3km] ),
                FILTER (
                    'Location',
                    [Main location] = SELECTEDVALUE ( Location[Main location] )
                )
            )
    )
)

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.