Search code examples
powerbidax

Filtering a table with condition involving another table in DAX

I tried to create a measure to calculate all clicks made by users registered within the last 15 days

NewUserClicks = 
    CALCULATE(
        COUNTROWS(
            DISTINCT('v_clicks'[unique_click_id])
        ),
        FILTER(
            ALL('v_user'),
            'v_user'[date_created_at] >= MIN('calendar'[Date]) - 15 && 'v_user'[date_created_at] <= MAX('calendar'[Date])
        ),
        USERELATIONSHIP('v_clicks'[unique_user_id], 'v_user'[unique_user_id])
    )

However, this produces numbers bigger than simply counting 'v_clicks'[unique_click_id] without any filter. I'm puzzled by this behaviour. I'm aware the 'ALL' in the filter clause has something to do with it, but I'm not sure how.

Does anyone have any insight?

Update: Data model enter image description here

Relationships enter image description here

Edit: Without any filter enter image description here

With filter: enter image description here

Solution

  • Will need to see your data model but giving it a go in the dark. Try:

    NewUserClicks = 
  var minD = MIN('calendar'[Date])
  var maxD = MAX('calendar'[Date])

  var users =
    CALCULATEDTABLE(
      DISTINCT('v_user'[unique_user_id]),
      REMOVEFILTERS('calendar'),
      'v_user'[date_created_at] >= minD - 15 && 
      'v_user'[date_created_at] <= maxD
    )

  return
    CALCULATE(
      DISTINCTCOUNT('v_clicks'[unique_click_id]),
      'v_clicks'[unique_user_id] IN users 
   )