DAX options to filter 1 side on 1:M relationship

Question

I have salesman dimension and sales fact with 1:M relationship on salesman_id.

I am trying to create a measure for count of salesman that have made sales in location is 6.

CALCULATE (
      DISTINCTCOUNT ( Salesman[SalesmanKey] ),
      Sales,
      Sales[LocationId] = 6
)

I think this is not working because the filter doesn't flow from sales into salesman table.

I could change the filter direction as both but I'm looking at other option like using DAX CALCULATE with CROSSFILTER. Is there any other option like using CALCULATETABLE?

Agustin Palacios Agustin Palacios · Accepted Answer · 2021-01-23T21:00:55

You can use the following Dax formula to achieve your goal:

Measure = 
    CALCULATE(
        DISTINCTCOUNT( Salesman[SalesmanKey] ),
        CROSSFILTER( Sales[salesman_id], Salesman[SalesmanKey], Both ),
        Sales[LocationId] = 6
    )

However I recomend you using the salesman id from the fact table:

Measure = 
    CALCULATE(
        DISTINCTCOUNT( Sales[salesman_id] ),
        Sales[LocationId] = 6
    )

Edit: i added the option using the calculatetable:

Measure = 
CALCULATE(
    DISTINCTCOUNT( Salesman[SalesmnaKey] ),
    CALCULATETABLE(
        Sales,
        Sales[LotacionId] = 6
    )
)

DAX options to filter 1 side on 1:M relationship

1 Answers