Forum Discussion
Forms - PowerBI Help - Combine multiple rows based on 1 field and merge results
Hi Soupy127 ,
Please see the file attach. What I have done:
- Unpivot the questions and answers
- I assume that you have the date of the form also
- Add the following measure:
Last values = var temptable = TOPN(1, 'Table', 'Table'[Date], DESC) Return MAXX(temptable, 'Table'[Answer])Final result:
The bottom table is the one with the full questions and dates the top one is the one with only the last results.
Please let me know if you need further assistance
OK,
Sorry I missunderstood tough you wanted the last response.
Just try this measure since in Yes is always after No (Alphabetically)
Last values = MAX('Table'[Answer])And if you want some additional information on top of this you can have this additiona formula:
Super UserHi Soupy127 ,
Please see the file attach. What I have done:
- Unpivot the questions and answers
- I assume that you have the date of the form also
- Add the following measure:
Last values =
var temptable = TOPN(1, 'Table', 'Table'[Date], DESC)
Return
MAXX(temptable, 'Table'[Answer])Final result:
The bottom table is the one with the full questions and dates the top one is the one with only the last results.
Please let me know if you need further assistance
Thanks Miguel again for your help.
But I dont think the solution above will work, i.e. in your example Q2 was marked yes by by ID 1 but in the final results it shows as a "No".
What im looking to achieve is:
1. Painter comes along and ticks Yes to all of his relevent questions (lets say 1-5), he will not be presented with 6-10 as they are not relevent to him, so on the results line it will show 1-5 with a Yes and 6-10 as a No. (as No is the default value).
2. Tiler then comes along and he is presented with questions 6-10 and he Marks them as Yes and he is not presented with 1-5 - so on his Forms result it will show No for 1-5 and a yes from 6-10.
So need for Yes to take precedent over all of the Nos' (hoping this makes since). So in other words if there is a Yes in the Question column for that Room it will show a Yes (not just based of last answer).
Hoping this makes since, there may be a better way to do this that I havent thought off.
I.e. in the above example the row for that Room should all be Yes's as there are Yes's in each of the columns.
- MFelix
OK,
Sorry I missunderstood tough you wanted the last response.
Just try this measure since in Yes is always after No (Alphabetically)
Last values = MAX('Table'[Answer])And if you want some additional information on top of this you can have this additiona formula:
Miguel, you are an absolute Legend.
That worked exactly as expected, thanks very much again for your help!