The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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")
)
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")
)
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.
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
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.