Forum Discussion
Simple filter not finding record
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
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")
)
4 Replies
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.
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
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.
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")
)
