DAX Query with multiple filters in powerbi

Question

I have two tables 'locations' and 'markets', where, a many to many relationship exists between these two tables on the column 'market_id'. A report level filter has been applied on the column 'entity' from 'locations' table. Now, I'm supposed to distinctly count the 'location_id' from 'markets' table where 'active=TRUE'. How can I write a DAX query such that the distinct count of location_id dynamically changes with respect to the selection made in the report level filter?

Below is an example of the tables:

locations:

| location_id | market_id | entity | active |
|-------------|-----------|--------|--------|
| 1           | 10        | nyc    | true   |
| 2           | 20        | alaska | true   |
| 2           | 20        | alaska | true   |
| 2           | 30        | miami  | false  |
| 3           | 40        | dallas | true   |

markets:

| location_id | market_id | active |
|-------------|-----------|--------|
| 2           | 20        | true   |
| 2           | 20        | true   |
| 5           | 20        | true   |
| 6           | 20        | false  |

I'm fairly new to powerbi. Any help will be appreciated.

I think Murray's solution will work. If not, it always help if you add some sample output you wants from the given sample data. — mkRabbani
Agreed, I also think it works. Could you please explain why the solution is not correct? Seems to return the correct result, i.e. Entity = Alaska -> market_id = 20 -> returns 2 distinct location_ids (2 and 5) where active = True. — Justyna MK

Murray Foxcroft Murray Foxcroft · Accepted Answer · 2020-07-22T06:27:25

Here you go:

DistinctLocations = CALCULATE(DISTINCTCOUNT(markets[location_id]), markets[active] = TRUE())

DAX Query with multiple filters in powerbi

1 Answers