Forum Discussion
Search a specific table based on a value
Hello everyone,
Im fairly new to Power BI and DAX but I have a challenge which im struggling with.
Table example:
Challenge is trying to get the "When" collumn dinamically searching the Sprint Table based on the Project value.
When I have only one project it's simple:
SprintClosureOrder =
var __ClosureDate = 'Main Table'[Created date]
return
MAXX(
FILTER(
'Sprint Table for XX',
AND(
'Sprint table for XX'[Start Date] <= __ClosureDate,
__ClosureDate <= 'Sprint Table for XX'[End Date]
)
),
'Sprint Table for XX'[Sprint]
)
And this is correctly returning the expected value. However, I've been unabel to add variables that select the correct table to search the expected sprint value.
Has anyone had a similar challenge and was able to overcome it?
Thank you.
NewbieQA - Firstly you should add a project column to each sprint table, then append all of them together in Power Query, this will make the DAX much less complex. Here's some guidance on that: https://learn.microsoft.com/en-us/power-query/append-queries
Once that is done, you can create a variable for the selected sprint,
VAR _project = SELECTEDVALUE( 'Sprint table'[Project] )and add that into your FILTER conditions:
FILTER( 'Sprint Table', AND( 'Sprint table'[Start Date] <= __ClosureDate, __ClosureDate <= 'Sprint Table'[End Date], 'Sprint table'[Project] = _project ) )You might need to change the 'Sprint table' for your 'Main table' columns in this filter code - depending on how your relationships are working.
If this works, please accept as the solution, it helps with visibility for others with the same challenge.
NewbieQA - Just change the filter argument to this:
FILTER( 'Sprint Table', 'Sprint table'[Start Date] <= __ClosureDate && __ClosureDate <= 'Sprint Table'[End Date] && 'Sprint table'[Project] = _project )If this works, please accept as the solution, it helps with visibility for others with the same challenge.
4 Replies
- mark_endicott
Super User
NewbieQA - Firstly you should add a project column to each sprint table, then append all of them together in Power Query, this will make the DAX much less complex. Here's some guidance on that: https://learn.microsoft.com/en-us/power-query/append-queries
Once that is done, you can create a variable for the selected sprint,
VAR _project = SELECTEDVALUE( 'Sprint table'[Project] )and add that into your FILTER conditions:
FILTER( 'Sprint Table', AND( 'Sprint table'[Start Date] <= __ClosureDate, __ClosureDate <= 'Sprint Table'[End Date], 'Sprint table'[Project] = _project ) )You might need to change the 'Sprint table' for your 'Main table' columns in this filter code - depending on how your relationships are working.
If this works, please accept as the solution, it helps with visibility for others with the same challenge.
Hello mark_endicott
Thank you for your quick feedback.
Indeed merging the DB's helped but it seems I cannot add thisWithout triggering an error since im now providing more arguments than what the "AND" function can handle.
Any ideas on how to work around this? I've tried a couple of possible solutions but unable to sort this out.- mark_endicott
NewbieQA - Just change the filter argument to this:
FILTER( 'Sprint Table', 'Sprint table'[Start Date] <= __ClosureDate && __ClosureDate <= 'Sprint Table'[End Date] && 'Sprint table'[Project] = _project )If this works, please accept as the solution, it helps with visibility for others with the same challenge.
