Forum Discussion
Helper IReturning a value if multiple criteria met
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
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.
8 Replies
- Idrissshatila
Super User
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.
ksteeverI'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.
- 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.
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!
