topic Re: Getting values from a column associated with a value on the same row in PowerBi in DAX Commands and Tips

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

Anonymous — Thu, 18 Feb 2021 14:56:29 GMT

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 id	string id	string
60	200	Current set
60	100	80%
30	200	Current set
30	50	John D.
100	80	VIEW
25	200	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 id	string
60	Current set
60	80%
30	Current set
30	John D
25	Current 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.

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

lbendlin — Thu, 18 Feb 2021 02:39:49 GMT

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.

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

Anonymous — Thu, 18 Feb 2021 14:27:33 GMT

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

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

lbendlin — Thu, 18 Feb 2021 14:31:10 GMT

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

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

Anonymous — Thu, 18 Feb 2021 14:57:34 GMT

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.