How to copy a value from one row to another based on unique ID

[SusD]

Dear all 

First of all thanks for helping me out. I've been struggling with this issue for quite some time and I do not seem to be getting a satisfactory result. 

The question goes as follows: I'm getting some filled in questionnaires through an SQL server and the data that comes in is rather unpolished. The questionnaire is built out of two parts. One in which you fill in a simple MC question (good, bad, perfect...) and one in which you can go a bit more into detail. Both questions are linked to a unique id. The number of rows is dependant on the input and is therefore variable. The problem is that for the analytical purposes, the answer to the MC question needs to come back in the other part. Even though I tried explaining it as good as possible, I've added some screenshot to provide clarity 🙂 

I've found a potential solution on the forum which stated that grouping might work. I've tried it and even though it works, the dataset is too slow to work with after I've done it and I got complaints from the ITC department that I was clogging the server. The dataset I'm currently working in is around 200.000 rows and 50 columns. The one below is of course simplified. 

Thanks a lot! 

Sus

     

[PhilipTreacy]

Hi @SusD 

Download sample PBIX with this data and code

Are these ID's always the same with the same assigment i.e. is 22000 always Good, is 22001 always Perfect etc?

If you know the ID and what text string to assign it then you could do this in Power Query by creating a Custom Column

= if [ID] = 22000 or [ID] = 37800 then "Good" else if [ID] = 22001 then "Perfect" else "Bad"

Or better to do it in DAX using a measure

Score = 

VAR _ID = SELECTEDVALUE('Table'[ID])

RETURN

SWITCH(

    TRUE(),   

    _ID = 22000 || _ID = 37800, "Good",

    _ID = 22001, "Perfect",

    _ID = 23000, "Bad",

    ""
)

Regards

Phil

[SusD]

Hi Philip, 

Unfortunately this is not the case. I have around 200.000 unique IDs and they are not linked to the result. 

Thanks for the help! 

[amitchandak · Accepted solution]

@SusD , Create a new  column like this in DAX

maxx(filter(Table, [ID] = earlier([ID])),[Score])

[Naveen_Menon]

Any idea on how to do the same solution in power query?

Except Fill Up and Down.

[SusD]

Quick update:

I'm realising now that it was a calculated column. Working like a charm now. Thanks!

[SusD]

Hi there, 

Thanks for the help! However, I do seem to be getting an expression error. Any idea what might be causing the issue? I've added printsceeen with updated column names.