topic Re: Column value from row in DAX Commands and Tips

Column value from row

deaconb — Fri, 03 May 2024 23:07:59 GMT

We have training data that looks like below:

Name	Role	ResponseID	Trainer
A	Trainee	7125417	Z
Z	Trainer	7125417
A	Trainee	7125461	Y
Y	Trainer	7125461
B	Trainee	7125955	X
X	Trainer	7125955
B	Trainee	7125978	X
X	Trainer	7125978

A Trainee can complete several different training events either with a different Trainer each time or the same trainer over multiple times. Each time a Trainee compelete a training, the training event is stored as a transaction in the database by "ResponseID"; these increment up 1 for each training event, so it can be used to order things chronologically 1-N, where item 1 was completed before item 2, etc. The trainer and trainee are logged to the same "ResponseID" and differentiated by the "Role" column.

I'd like to have the data as depicted above in the "Trainer" column. So essentially, I want to have a single row of each "ResponseID" by making the "Trainer" a column instead of keeping it as "second" row.

I'm pretty new to Power BI and I'm just at a loss for how to even accomplish this. Any help or guidance with this is greatly appreciated!!

Re: Column value from row

DataNinja777 — Sat, 04 May 2024 03:20:05 GMT

Hi deaconb

There are many ways to achive your required output, and one example is as shown below:

I attach an example pbix file.

Re: Column value from row

tamerj1 — Sat, 04 May 2024 03:34:04 GMT

Hi deaconb
Hope this is what you're looking for

Table 2 = SUMMARIZE ( 'Table', 'Table'[ResponseID], "Trainee", MAXX ( FILTER ( 'Table', 'Table'[Role] = "Trainee" ), 'Table'[Name] ), "Trainer", MAXX ( FILTER ( 'Table', 'Table'[Role] = "Trainer" ), 'Table'[Name] ) )

Re: Column value from row

deaconb — Sun, 05 May 2024 00:40:55 GMT

hey - this result is exactly what I'm looking for. However, when I insert it into my query as a column, it gives me an error of a cyclic reference.

Re: Column value from row

tamerj1 — Sun, 05 May 2024 11:54:32 GMT

deaconb

What is your query?

Re: Column value from row

Anonymous — Mon, 06 May 2024 09:45:39 GMT

Hi deaconb ,
Thanks to tamerj1 and DataNinja777 for thier methods.
Here's how I solved it
Create column

Trainer = VAR _result = CALCULATE( MAX('Table'[Name]), FILTER( ALLEXCEPT( 'Table', 'Table'[ResponseID] ), 'Table'[Role] = "Trainer" ) ) RETURN IF( 'Table'[Role] = "Trainee", _result, BLANK() )

Create a calculated table

Result = SELECTCOLUMNS( FILTER('Table','Table'[Trainer] <> BLANK()), "ResponseID",'Table'[ResponseID], "Trainee",'Table'[Name], "Trainer",'Table'[Trainer] )

Final output

Best regards,

Albert He

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

Re: Column value from row

deaconb — Mon, 06 May 2024 13:09:56 GMT

The source data is a full dataset of training information. I truncated the data in my example to only the columns I care about/need to work with.

When I applied your logic as a Custom Column step in Power Query, I received this error:

Expression.Error: A cyclic reference was encountered during evaluation.

When I applied your logic as a new Column in Power BI table view, I received this error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Column value from row

deaconb — Mon, 06 May 2024 13:21:43 GMT

Hi DataNinja777 this works as a new Column in Power BI table view, but it isn't working as a Custom Column step in Power Query (I understand they are different).

I'd prefer to do this in Power Query so I can then delete the "Trainer" row all togther, after the new column is created, which makes the remaining dataset 50% less rows as the only difference between the raw dataset rows is the Trainer row and Trainee row.

Do you know what modifications I'd need to make to get this to work through Power Query?

Re: Column value from row

tamerj1 — Mon, 06 May 2024 13:31:33 GMT

deaconb
You can simply group by Response ID and use if statement in the aggregation.

Re: Column value from row

DataNinja777 — Mon, 06 May 2024 13:54:35 GMT

Hi deaconb

You have your required output in dax formula from multiple people, but if you want to use Power Query instead, you can create a table like below using Power Query Ribbon clicking technique.

Best regards,

Re: Column value from row

deaconb — Mon, 06 May 2024 16:51:30 GMT

Thanks. I'll just use the dax output.