powerbi - 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?

question from:https://stackoverflow.com/questions/65864106/dax-options-to-filter-1-side-on-1m-relationship

Answer 1

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