Forum Discussion
Helper IReturn value based on column contents in another table
I have a huge table with information from out Jira instance. Inside that table (named Jira) I have three key columns that all contain string values (Parent, Software, Data). Software and data will only list true or false, but it is a string value.
There could be hundreds of recurring values in the Parent column. Based on this, I've setup another table with a single column that has a 1:Many relationship back to the Jira table (pictured below) that grabs all the unique values using the below code.
Parent = VALUES(Jira[Parent])
I want to represent that when looking at the value in the parent column the formula then looks at the software and data columns and if the column/row is true for both, return "both". If only one is true, return thatt header (only software or data). If both are false, return "none". Is this possible? I've tried to add another column to the new table, but I cant call back to the original (guessing because of the relationship?)
3 Replies
- Daniel29195
Community Champion
Hello ksteever
i just need to get understand the logic.
in the original table, i can have many rows with the same parent , correct ?
this means
parent data software
p1 true true
p1 false false
p1 true false
nowin the new table, we have unique parent values :
parent
p1
when you are creating the new calculated column, p1 is related to 3 rows, with different values,
so the result will depend on which row ?
ksteeverCorrect. The software column and data column will never both be true at the same time in the original table. Here's an example table if you wanna play with a small chunk of data since I cant attach xls/csv here.
Parent key Softwaredev DataEng ELS-5 False False ELS-14 False False ELS-23 False False ELS-1 False False ELS-54 False False ELS-13 False False ELS-21 False False ELS-10 False False ELS-7 False True ELS-12 False False ELS-31 False False DRS-126 False False DRS-125 False False DRS-123 False False DRS-50 False False DRS-117 False False DRS-3 False False ELS-5 False False ELS-14 False False ELS-23 False False ELS-1 False False ELS-54 False False ELS-13 False False ELS-21 False False ELS-10 False False ELS-7 False False ELS-12 False False ELS-31 False False DRS-126 False False DRS-125 False True DRS-123 False False DRS-50 False True DRS-117 False False DRS-3 False False ELS-5 False False ELS-14 False False ELS-23 False False ELS-1 False False ELS-54 False False ELS-13 False False ELS-21 False True ELS-10 False False ELS-7 False False ELS-12 False False ELS-31 False False DRS-126 False False DRS-125 False True DRS-123 False False DRS-50 False False DRS-117 False True DRS-3 False False ELS-5 False True ELS-14 True True ELS-23 False False ELS-1 False False ELS-54 False False ELS-13 False False ELS-21 False False ELS-10 False False ELS-7 True False ELS-12 False False ELS-31 False False DRS-126 False False DRS-125 False False DRS-123 False False DRS-50 True False DRS-117 False False DRS-3 False False DRS-126 False False DRS-125 False False DRS-123 False False DRS-50 True False DRS-117 False False DRS-3 False False DRS-117 False True Here's a list of the unique values:
Parent key unique ELS-5 ELS-14 ELS-23 ELS-1 ELS-54 ELS-13 ELS-21 ELS-10 ELS-7 ELS-12 ELS-31 DRS-126 DRS-125 DRS-123 DRS-50 DRS-117 DRS-3 - ksteever
Daniel29195 any insights?
