Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ksteever
Helper I
Helper I

Return 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 3
Daniel29195
Super User
Super User

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 ? 

 

 

 

Correct. 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 keySoftwaredevDataEng
ELS-5FalseFalse
ELS-14FalseFalse
ELS-23FalseFalse
ELS-1FalseFalse
ELS-54FalseFalse
ELS-13FalseFalse
ELS-21FalseFalse
ELS-10FalseFalse
ELS-7FalseTrue
ELS-12FalseFalse
ELS-31FalseFalse
DRS-126FalseFalse
DRS-125FalseFalse
DRS-123FalseFalse
DRS-50FalseFalse
DRS-117FalseFalse
DRS-3FalseFalse
ELS-5FalseFalse
ELS-14FalseFalse
ELS-23FalseFalse
ELS-1FalseFalse
ELS-54FalseFalse
ELS-13FalseFalse
ELS-21FalseFalse
ELS-10FalseFalse
ELS-7FalseFalse
ELS-12FalseFalse
ELS-31FalseFalse
DRS-126FalseFalse
DRS-125FalseTrue
DRS-123FalseFalse
DRS-50FalseTrue
DRS-117FalseFalse
DRS-3FalseFalse
ELS-5FalseFalse
ELS-14FalseFalse
ELS-23FalseFalse
ELS-1FalseFalse
ELS-54FalseFalse
ELS-13FalseFalse
ELS-21FalseTrue
ELS-10FalseFalse
ELS-7FalseFalse
ELS-12FalseFalse
ELS-31FalseFalse
DRS-126FalseFalse
DRS-125FalseTrue
DRS-123FalseFalse
DRS-50FalseFalse
DRS-117FalseTrue
DRS-3FalseFalse
ELS-5FalseTrue
ELS-14TrueTrue
ELS-23FalseFalse
ELS-1FalseFalse
ELS-54FalseFalse
ELS-13FalseFalse
ELS-21FalseFalse
ELS-10FalseFalse
ELS-7TrueFalse
ELS-12FalseFalse
ELS-31FalseFalse
DRS-126FalseFalse
DRS-125FalseFalse
DRS-123FalseFalse
DRS-50TrueFalse
DRS-117FalseFalse
DRS-3FalseFalse
DRS-126FalseFalse
DRS-125FalseFalse
DRS-123FalseFalse
DRS-50TrueFalse
DRS-117FalseFalse
DRS-3FalseFalse
DRS-117FalseTrue

 

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

@Daniel29195 any insights? 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.