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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Getting values from a column associated with a value on the same row in PowerBi

What I am trying to do is to get a value on the same row from one column that is associated with a value on another column on the same table.

Currently I have 4 columns. As shown below, I'll call this table the "Objects" table:

 

file idstring idstring
60200Current set
6010080%
30200Current set
3050John D.
10080VIEW
25200

Current set

 

I am currently trying to find a way where PowerBI looks for the "string id", then find the "file id" associated with the value id.

Finally a new column or table will return all values in the "string" column associated with the file id that it found.

 

For example, it looks up the "string ID" 200 then finds the "file ID" 60, 30, 25. Once it gets those ID's it will return the values in the "string" column that are associated with the file ID. So in this example it will return everything except the cell that has "VIEW".

 

I've tried writing an If statement inside of an if statement but that did not work.

 

 

 

 

custom_column = IF(Objects[file id] = (IF(Objects[string id]=200, Objects[file id])), Objects[string])

 

 

 

 

This only returned the value "Current set"

Is there another DAX function I can use to populate a column with the information i need or populate it in a different table?

 

My expected outcome would be as follows

 

if its a different table:

file idstring
60Current set
6080%
30Current set
30John D
25Current set

 

or a custom column next to the original table:

custom_column
Current set
80%

Current set

John D.
 
Current set

 

Thank you in advance.

4 REPLIES 4
lbendlin
Super User
Super User

Show your expected outcome. It is not clear from your description.

Anonymous
Not applicable

Again sorry that I was not clear and cause a bit of confusion. I tried replying to your post but I constantly received an error that said invalid html. So i edited my original post to reflect what I expected.

lbendlin
Super User
Super User

You need to get out of the current filter context for that.

 

custom_column = 
var s = Objects[string id]
var f = SUMMARIZE(filter(Objects,Objects[string id]=s),Objects[file id])
return CALCULATE(CONCATENATEX(Objects,Objects[string],","),All(Objects),Objects[file id] in f)

 

 

By the way your statement "So in this example it will return everything except the cell that has "VIEW"." is not entirely correct.

 

 

lbendlin_1-1613615981660.png

 

 

 

Anonymous
Not applicable

Thank you so much for this. I was hoping to get each value that was associated to the same file id as current set on a seperate row. I would probably need to create a new table and do this? Sorry if i wasnt clear.

 

Unless this would also take a cell thats blank and then I would be able to break down the column by using a comma delimiter to get everything on cell seperated out to their own cells

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors