Simple filter not finding record

[chipchidster]

I'm sure this is something simple but i cannot for the life of me work out why this isn't working!

I have two tables - sprint and sprint summary.

Sprint contains a row for each item in a sprint

Sprint Summary, as the name suggests - summarises the data in the sprint table including a load of calculated fields

I am trying to do a lookup from Sprint Summary on Sprint to get the sprint id using the following DAX:

CALCULATE(MIN(sprint[sprint_id]),
          FILTER(ALL(sprint),
                sprint[project_key] = sprint_summary[project_key] &&
                sprint[year_week] = sprint_summary[year_week] &&
                sprint[in_scope] = "Yes")
)

While this finds a sprint id for almost all sprint periods - every so often it returns empty.  I've manually checked the sprint table and can find a row that it should find.  Likewise, i have changed the dax to output the var's its using (project key and year week) to filter the rows on the sprint table, and they are correct also. 

So now I am at a complete loss - I can't understand why it won't find the rows on the sprint table

[chipchidster · Accepted solution]

Urgh okay - so in further developments, i've resolved this (i think) but I am not sure I understand how or why.

Essentially I have replaced my CALCULATE with the MINX that was suggested in the first reply and it has started working!  If anyone wants to point out why this would be the case I would love to know (i'm sure i am being exceptionally dense here as both feel interchangable)

MINX(
    FILTER(ALL('sprint'),  
           'sprint'[project_key] = 'sprint_summary'[project_key] &&
           'sprint'[year_week] ='sprint_summary'[year_week] &&
           'sprint'[in_scope]="Yes"),
    'sprint'[sprint_id])

CALCULATE(MIN(sprint[sprint_id]),
          filter(sprint, 
                 sprint[project_key] = sprint_summary[project_key] &&
                 sprint[year_week] = sprint_summary[year_week] &&
                 sprint[in_scope] = "Yes")
          )

[chipchidster]

Apologies, I forgot to mention - this is for a calculated column not a measure.  Below is the data I am working with to help bring this to life a little more.

The calc column is on Sprint Summary and the row data looks something like (i've removed some of the columns):

max_sprint_date	project_key	sprint_id	year_week
04/03/2024 23:59:59	SCV		2024_10

The sprint table row data 

issue_id	sprint_id	in_scope	project_key	year_week
12345	2959	yes	SCV	2024_10
67891	2959	yes	SCV	2024_10

In my real dataset i have 9 rows of data that should match my search critera from the sprint_summary table (e.g. year_week = 2024_10, project_key = SCV and in_scope = "Yes").  When i manually filter the sprint table i can see them, but my dax to populate sprint_id is just not freturning anything.

[Anonymous]

Hi  @chipchidster ,

You can try the following dax

Measure =
MINX(
    FILTER(ALL('sprint'),  'sprint'[project_key]=MAX('sprint_summary'[project_key])&&'sprint'[year_week]=MAX('sprint_summary'[year_week])&&'sprint'[in_scope]="Yes"),'sprint'[sprint_id])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

You have not specified whether your formul is a calculated column or a measure.  Assuming it is a measure, Revise it to:

=CALCULATE(MIN(sprint[sprint_id]),sprint[in_scope] = "Yes")

If this does not help, then share some data to work with and show the expected result.