Comparing data to series of column titles to return other data

[jpeterson]

I have a direct query with many columns.  One column is "Workflow Current State."  I need to take the data in that column and compare it to 5 other columns (titled "Owner", "Approver 1", etc.) to return the results of whatever is in those other 5 columns. 

For example, if the Workflow Current State is "Approver 1," I need it to return the fact that Mary in the "Approver 1" column elsewhere in the query. 

Thanks!

[v-jayw-msft]

Hi @jpeterson ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @jpeterson

Ypu need to use a switch formula something similar to this

Column = SWITCH(
TABLE[WORK FLOW CURRENT STATE] ;
"VALUE 1" ; TABLE[COLUMN1] ;
"VALUE 2" ; TABLE[COLUMN2] ;
"VALUE 3" ; TABLE[COLUMN3] ;
"VALUE 4" ; TABLE[COLUMN4] ;
"VALUE 5" ; TABLE[COLUMN5]
)

Should give expected result.

[kentyler]

I can't tell for sure with what you've told us, but I suspect you have issues in your data model that will cause the DAX you need to write to be much more complicated...

So one way to address the problem is to look at your data model

the other way would be to write a DAX forumla that

1) gets the current value (the value in the row being processed) from Workflow current state and

2) uses that value to look up values in the five other columns using, perhaps, LOOKUPVALUE()

3) returns the result to ? its now clear what you want to do if you find matches in the other 5 columns.

If you'd like to provide some small sample tables, or upload a sample power bi file, I'll take a closer look.

I would also be open to a screen share later this afternoon... sometimes problems like this are symptoms of a variety of issues all at once, and its easier to address those in a screen share. If that seems like it might be helpful email me ken@8thfold.com

Power BI is easy to learn, but it has hard parts. I'm a personal Power Bi Trainer I learn something every time I answer a question

[jpeterson]

Hi.  I can't share the actual data, but here's an image of what it looks like.  What I want to add to my table visualziation is a column that says where the current responsibility for the document lies.

[kentyler · Accepted solution]

Thanks, that helps.

I think it would help to have a fact table that had a link to the document, a date, and an approver name

with one record each time the document moved to a different approver.

Then to get the document's current location you just retrieve the max date for that document and get that record.

A table like that would also let you do a lot of cool stuff because it would know how long the document is taking at each approver.

But,

It looks like you could also just add a calculated column with a 3 part nested IF()

IF(not(isblank(approver 3 name)),approver 3 name,
         IF(not(isblank(approver 2 name)),approver 2 name,
                 IF(not(isblank(approver 1 name)),approver 1name,
                          "")))
they are tricky to write (not sure all my parentheses match in this example, but straightforward in their logic.

Power BI is easy to learn, but it has hard parts. I'm a personal Power Bi Trainer I learn something every time I answer a question