Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, everyone!
I'm working with a form that is automatically downloaded from a server and it consists of a table with responses from multiple different forms. Each row of the table is a different question/response, and each response has an ID associated with it. So, the columns basically tells me: Response ID Number; What kind of form it is; What's being asked; The response of the question.
One of the questions is a dropdown list of 24 locations to choose from (not editable by the user). In each row that contains the answer to this question, I can find the location and the ID associated with the response and, therefore, I can know where the response is coming from.
I've created a conditional column that is filled when it finds one of these 24 locations on the response column (otherwise it says "verify"), but that only fills the row with the location question. I need to have all of the lines from the same response ID filled with the location name. Something like, having PowerBI find the value of "response column" if "response ID" = X and if "response column" value is one of the 24 locations, and then populating the rows in a new column with the value of "response column" if "response ID" = X
PS: in portuguese, which is the language of the table, verify = verificar and location = localidade/local
Can anyone help me? Thanks in advance!
Solved! Go to Solution.
You need to add the column using that expression in "Data" pane using "New Column" NOT in "Power Query Editor".
Also please note in the below expression LookupValue is name of the table I've stored sample data, in your case it should be your tablename. As LookupValue is a DAX keyword as well, that's why it was mentioned in single quote (I might have used another name to avoid confusion)
Check = CALCULATE(MAX('LookupValue'[Answer]),FILTER(ALL('LookupValue'),'LookupValue'[Question]="Location" && 'LookupValue'[ResponseID] = EARLIER([ResponseID])))
Proud to be a PBI Community Champion
Hello, everyone!
I'm working with a form that is automatically downloaded from a server and it consists of a table with responses from multiple different forms. Each row of the table is a different question/response, and each response has an ID associated with it. So, the columns basically tells me: Response ID Number; What kind of form it is; What's being asked; The response of the question.
One of the questions is a dropdown list of 24 locations to choose from (not editable by the user). In each row that contains the answer to this question, I can find the location and the ID associated with the response and, therefore, I can know where the response is coming from.
I've created a conditional column that is filled when it finds one of these 24 locations on the response column (otherwise it says "verify"), but that only fills the row with the location question. I need to have all of the lines from the same response ID filled with the location name. Something like, having PowerBI find the value of "response column" if "response ID" = X and if "response column" value is one of the 24 locations, and then populating the rows in a new column with the value of "response column" if "response ID" = X
PS: in portuguese, which is the language of the table, verify = verificar and location = localidade/local
Can anyone help me? Thanks in advance!
HI @gbrmdc,
If you mean fill column with answers column value and replace blank value as 'verify'. you can try to use below formula:
#"Added Custom" = Table.AddColumn(#"preview step", "custom", each if [Column1.answer] <> "" then [Column1.answer] else "verify")
>>which is the language of the table, verify = verificar and location = localidade/local
You can modify regional setting in options or choose advanced download option to choose a particular language version of power bi desktop.
Regards,
Xiaoxin Sheng
Hello @v-shex-msft!
Thanks for your reply 🙂
What I meant was create a new column and have the location answer (in the picture I sent, they are "Mutuca" and "Pico") replicated to the other lines with same Response ID number
Is that possible?
"Mutuca" and "Pico" has different Response ID as per the screenshot.
Proud to be a PBI Community Champion
Hi @PattemManohar,
Yes, because they're associated with different form responses. What I need is to have the other lines with the same Response ID as "Mutuca" to say "Mutuca" instead of "Verify" in the Location or a new personalized column, the lines with same Response ID as "Pico" to say "Pico" and so on
Could you please post your expected output based on the Input screenshot or any sample input & output data will also be fine..
Proud to be a PBI Community Champion
It should look like this after adjustments:
Could you please try the following...
Add a "New Column" using below expression...
Check = CALCULATE(MAX('LookupValue'[Answer]),FILTER(ALL('LookupValue'),'LookupValue'[Question]="Location" && 'LookupValue'[ResponseID] = EARLIER([ResponseID])))
Proud to be a PBI Community Champion
Dear @PattemManohar,
Thanks for your help!
I am getting a "token literal expected" error when I try to add this formula:
If I click on "show error", it points to the first apostrophe on 'LookupValue'
Regards,
Gabriel
You need to add the column using that expression in "Data" pane using "New Column" NOT in "Power Query Editor".
Also please note in the below expression LookupValue is name of the table I've stored sample data, in your case it should be your tablename. As LookupValue is a DAX keyword as well, that's why it was mentioned in single quote (I might have used another name to avoid confusion)
Check = CALCULATE(MAX('LookupValue'[Answer]),FILTER(ALL('LookupValue'),'LookupValue'[Question]="Location" && 'LookupValue'[ResponseID] = EARLIER([ResponseID])))
Proud to be a PBI Community Champion
Hello!
I am having an issue with this expression in my application. I am looking to do essentially the same thing as OP, I have a report with survey data and each row is a different question. The individual surveys are identified by column "Survey Invitation ID" -- I would like to create a new column that fills with the answer to one of the questions based off of the survey ID (i.e. if customer fills out Survey ID 1 and indicates that they work at a university, the new column would identify each row labeled as Survey ID 1 as being a university customer). However, when I tried to use the formula provided in this post I am getting some false results. Attached is a screenshot of what the Survey ID column looks like, as well as the results of the new formula. The second Survey ID with 3 associated rows does not have a response value, but the formula is populating results regardless.
As mentioned above, use your table name in place of "LookupValue" (It is the table name I've created for testing your scenario)
Proud to be a PBI Community Champion
You are welcome !! Gabriel
Proud to be a PBI Community Champion
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |