Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
chipchidster
Resolver I
Resolver I

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

1 ACCEPTED SOLUTION
chipchidster
Resolver I
Resolver I

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

 

View solution in original post

4 REPLIES 4
chipchidster
Resolver I
Resolver I

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
Resolver I
Resolver I

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_dateproject_keysprint_idyear_week
04/03/2024 23:59:59SCV 2024_10

 

The sprint table row data 

issue_idsprint_idin_scopeproject_keyyear_week
123452959yesSCV2024_10
678912959yesSCV2024_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
Not applicable

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

vyangliumsft_0-1708495903084.png

 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors