Returning a value if multiple criteria met

[ksteever]

I'm attempting to do some data analysis for our leadership within Jira. The problem we're trying to solve is showing what teams are involved with what epic level tasks.  I've created a new table called "EpicTable" via this code

EpicTable = FILTER('Jira', 'Jira'[issue type] = "epic")

On each line of the data in the 'Jira' table, there is a column called "Epic name" which typically (but not always) will have one of the Epic names from the Epic table list.  What I need to do is query the Jira table where if there are ANY matches to EpicTable["Epic Name"] == Jira["Epic Name"] and assignee contains "Joe" or "John", it returns a true value. If there are NO matches to those assignee values, the formula would return false.

Here's an example dataset that I did in Excel

Team 1 = Joe, John

Team 2 = Jane

[v-xiandat-msft · Accepted solution]

Hi @ksteever ,

idrissshatila , His scenario is correct, but I'll provide an answer to the error aspect on my end:

This error message appears to be that you have duplicate values in your EpicTable which is causing the error, may I suggest using the following DAX when creating the EpicTable:

EpicTable = VALUES(Jira[Epic Name])

After that you can create calculated columns in the jira table:

Column = IF('Jira'[Epic Name] = RELATED('EpicTable'[Epic Name]) && 'Jira'[Assignee]in { "Joe","John"},"True","False")

And using this dax also requires a relationship between the two tables:

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ksteever]

Hi Xianda,

When I try to setup the relationship, I cannot get any cardinatlity or Cross filter direction to populate. I had to pivot from the name to the link as there could be duplicate names, but the links will always be unique. 

Epic Table created via clicking new table and pasting this in the code block

EpicTable = VALUES(Jira[Epic Link])

Then I tried to set the relationship since you mentioned the dependency.  Here's a screenshot of the manage relationships page:

And of course, the formula to add the new column isnt working either. Assigneename is a column that has the parsed out name of the person contained in the assignee column so it's just that content and nothing else.

[ksteever]

Not sure what I did to undo the above but this is not working. Thanks! 

[v-xiandat-msft]

Hi @ksteever ,

You could try to create a one-to-many relationship between the Epic link in Epic table and the Epic link in Jira, since the Epic link in Epic table is a virtual table built from the Epic link in Jira. like this:

To dax error , the main thing is that the RELATED function generally puts columns inside, and you can try putting columns into.

column = IF('Jira'[Epic Link] = RELATED('Epic table[Epic link]')&&'Jira'[AssigneeName] IN {"Joe","John"},"True","False")

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ksteever]

Unfortunately I cannot attach the PowerBI file as it's too large and contains private company information. I forgot I had already parsed out the contents of Assignee into the column AssigneeName which contains string values only. Here's an example from the Epictable table:

Here's the same line from the Jira table:

The assigneename value also is a text/text type+format.

[Idrissshatila]

Hello @ksteever ,

add the following column in the Jira table

Column = IF('Jira Table'[Epic Name] = RELATED('Epic Table'[Epic Name]) && 'Jira Table'[Assignee]in { "Joe","John"},"True","False")

check the attached file i worked on to develop the above.

[ksteever]

I'm getting the error below when I use this. The Assignee and the Epic name column is Text formatted as text. 

Here's the error:

[Idrissshatila]

@ksteever ,

you need to fill the names in your power bi file, or attach your file and i will make it work.