clean up data set with two records for one questionnaire

[Anonymous]

Hi,

I hope you can help me out with the following issue. 

I have a dataset with questionnaire data. For each questionnaire sent, a record is created with a date it was sent at (date sent). When someone fills out the questionnaire, a new record is created with a responde date (date response) added. I want to filter out the initial records of questionnaires that have been filled out but also keep the records that haven't been filled out yet and therefore only have one record. When I filter on date response, I would lose te records that haven't yet been filled out. 

The goal is to clean up the dataset and to create measures that would calculate the number of questionnaires sent out, how many were sent back and how many are still waiting for response. I have created an example:

questionnaire_id	subject	date sent	date response
100	a	1-1-2020
100	a	1-1-2020	14-1-2020
101	b	2-1-2020
102	c	6-1-2020
102	c	6-1-2020	8-1-2020

I want the result to be something like this:

100	a	1-1-2020	14-1-2020
101	b	2-1-2020
102	c	6-1-2020	8-1-2020

I hope you can help me out.

Regards,

Shipova

[V-lianl-msft]

Hi @Anonymous ,

Try to create a calculated column:

Column = CALCULATE(FIRSTNONBLANK('Table (2)'[date response],0),ALLEXCEPT('Table (2)','Table (2)'[questionnaire_id]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unfortunately this doesn't work. It filters out the questionnaires that don't have a response date yet but all other records are still there.

[amitchandak]

@Anonymous , Create new table

summarize(Table, Table[questionnaire_id], Table[subject], Table[date sent] , "date response" , lastnonblank(Table[date response],blank()))

or

summarize(Table, Table[questionnaire_id], Table[subject],"date sent", min( Table[date sent]) , "date response" , lastnonblank(Table[date response],blank()))

or use measures

min( Table[date sent])

lastnonblank(Table[date response],blank())

with questionnaire_id and subject

[Anonymous]

unfortunately this didn't work. I get the same table without the date response column.