Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 location Within 3km AAA BBB AAA BBB BBB CCC

Table 2: POI

 Location POI Category BBB LV Luxury BBB GAP Fashion BBB Kids Kids
1 ACCEPTED SOLUTION
Community Support

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 4
Community Support

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.

Super User

@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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Super User

@kbandito , what is the expected output

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors