Forms - PowerBI Help - Combine multiple rows based on 1 field and merge results

[Soupy127]

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.

[MFelix · Accepted solution]

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

[v-sdhruv]

Hi @Soupy127 ,
Just wanted to check if you had the opportunity to review the information provided by @MFelix .
If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[MFelix]

Hi @Soupy127 ,

Have you tried keeping the Forms value has you have but pickup the lastnonblankvalue for each of the questions? That would give you the correponding value based on the latest form for all the questions.

[Soupy127]

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]

No need to have the blanks, 

I will setup an example and send it tomorrow

[Soupy127]

Brill Miguel, thanks very much for your help! 

[MFelix · Accepted solution]

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

[Soupy127]

Brilliant Miquel, that makes since. I will test this afternoon and let you know if works :).

Thanks very much again for your help! 

[Soupy127]

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 · Accepted solution]

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:

[Soupy127]

Miguel, you are an absolute Legend.

That worked exactly as expected, thanks very much again for your help!