Is there a way to match/filter based on a list of values in DAX?

[mithrandir]

I'm using Power BI to gauge team performance from JIRA and I ahve run into a few situations where something like this would make things much easier. My current problem is that I have three tables: Issues, FixVersions and Components.
For discussions' sake:
Issues table consists only of "key", "createdDate" and "createdSinceLastRelease"
FixVersions consists of "key", "fixVersion" and "releaseDate"
Components consists "key" and "componentName"

The Issues table is the only one with unique rows and I'm trying to create a formula for "createdSinceLastRelease" to be true or false based on the other tables. What I'd like to do is get the "componentName(s)" for the current row in Issues, then get all of the "key(s)" with any of those "componentName(s)" from Components, then filter the FixVersions table based on that list of "key(s)" and take the MAX(FixVersions[releaseDate]) to compare against the current row's "createdDate". This might not be the best way to go about it, especially considering the number of steps, but I don't need to reuse any of the data produced between steps so I'd like to fit it into a single column.

Any help would be much appreciated!

[mithrandir]

This is what I have so far:

createdSinceLastRelease = 
IF([createdDate] >
    CALCULATE(MAX(FixVersions[releaseDate]),
        FILTER(FixVersions,
            CONTAINS(
                FILTER(ALL(Components),
                    CONTAINS(
                        FILTER(ALL(Components),
                            Components[key] = [key]),
                        Components[componentName],
                        Components[componentName])),
                Components[key],
                Components[key]))),
    1, 0)

Current problem is the last Components[key] for the value parameter in the last CONTAINS function returns an error stating:

"A single value for column 'key' in table 'Components' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

So, I don't know if there is a small tweak I can make or if I need to come from a different perspective. Maybe this isn't feasible?

[v-huizhn-msft]

Hi @mithrandir,

It return the error message because the relationship between  Issues and Components is one-to many information. Based on my understanding, you can just need the Issues and FixVersions table, please see the following formula.

createdSinceLastRelease = 
IF([createdDate] >MAX(RELETED(FixVersions[releaseDate]))1, 0)

Best Regards,
Angelia

[mithrandir]

Hey Angelia,

That's actually what I am currently using, but it's not as specific as I want to be. What I am trying to do is get the most recent "releaseDate" for only components of the current row. I didn't illustrate it very well, but in actuality most "issues" are not released yet. So, I need to know whether they have been logged since the last releaseDate that corresponds to them and exclude releaseDates that are not related.

I've also tried:

createdSinceLastRelease = IF([createdDate] >
    CALCULATE(
        MAX(FixVersions[releaseDate]),
        FILTER(ALL(FixVersions),
            FixVersions[key] IN
            FILTER(ALL(Components[key]),
                CONTAINS(
                    FILTER(ALL(Components),
                        Components[componentName] IN
                        FILTER(ALL(Components[componentName]),
                            CONTAINS(
                                FILTER(ALL(Components),
                                    Components[key] = [key]),
                                Components[componentName],
                                Components[componentName]))),
                    Components[key],
                    Components[key])))), 1, 0)

The problem with the above formula is that it still only returns one date for all rows. Thanks for the quick response!

-mithrandir

[mithrandir · Accepted solution]

I've figured it out! In the last FILTER() function, wrap [key] with an EARLIEST() function to force it to the current row and it works. Thanks again for the reply @v-huizhn-msft!

[mithrandir]

Here's some mock data to help illustrate what I'm trying to do.