Forum Discussion
Forms - PowerBI Help - Combine multiple rows based on 1 field and merge results
Hi All,
Hope you are well :)!
I have been tasked with creating a Microsoft Form which can be used as a checklist for checking progress of a construction project.
So it will consists of 2 text fields and a list of 50 questions (which default to No), the 2 text fields (might make them a dropdown list), will be the floor and appartment number.
It will then Display a list of trades and then will filter on this to show the relevent questions i.e. a painter will see only his questions.
The idea is that I can combine all of the different form results for a single appartment into 1 row so we can see the progress of that appartment.
I.E. on the excel spreadsheet generated by the form it will look something like the below were each appartment will have multiple rows:
Wondering if there is a way on PowerBI that I could combine the results to 1 row per Room which then combines all of the results from the questions (with 'Yes' taking precedence).
With the final idea being that I end up with something like this (with the Yes answers being green)
Sorry for the long post.
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:
10 Replies
Hi MFelix,
In that case, on the form would you remove the default value for each of the questions (so they are blank) and then do as you say above and it would only show the 'Yes' answers?
If so could you show me an example of were to put the 'lastnoblankvalue' as have never used it before 🙂Thanks again
- MFelix
Super User
No need to have the blanks,
I will setup an example and send it tomorrow
