Summarize answers to a recurring questionnaire by time intervals

[mthomeer]

Dear Community

Problem

We send a questionnaire about quality of life to our patients, who are treated for cancer. It is a questionnaire (https://qol.eortc.org/questionnaire/eortc-qlq-c30/) that is recurring at different time-intervals: baseline, month 3, month 6, month 12, month 24, month 36, month 48, and month 60.

An additional challenge is that the date of the answer to the questionnaire is not always the date when the question was sent, but is usually answered within one month.

Input table

PatientID	Date Questionnaire answered	Answer Q1
patient A	dd/mm/yyyy	5
Patient B	dd/mm/yyyy	4
patient A	dd/mm/yyyy	8
patient B	dd/mm/yyyy	2
....

Output

The output is to summarize the answers (mean) by each time-interval:

y-axis: mean "Answer Q1"

x-axis: baseline, month 3, month 6, month 12, month 24, month 36, month 48, and month 60.

I am just a simple doctor. I can't get this put together. But if the data can be displayed in this way, the care team can focus well on areas for improvement in the care of the patient with cancer.

I hope you can help me

Michiel

@mikekinsman @Nathaniel_C @AIB

[MFelix]

Hi @mthomeer ,

To what I can understand from your information you want to have your chart divided by the first response (baseline), second response (3 months), and so on is this correct?

You can do the following:

• Add a table with the periods:

Be aware that the ID is very importat for this solution.

• Add two measures:

RankDates =
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Date Questionnaire answered] ) ),
    ,
    ASC
)


Evaluation =
VAR tem_table =
    SUMMARIZE (
        'Table',
        'Table'[PatientID],
        'Table'[Date Questionnaire answered],
        'Table'[Answer Q1],
        "rank", [RankDates]
    )
RETURN
    AVERAGEX (
        FILTER ( tem_table, [rank] = SELECTEDVALUE ( Months_Respose[ID] ) ),
        'Table'[Answer Q1]
    )

Basically on the measure above what I'm doing is ranking the answers, so if the person answers all the questionaires you send them (no matter what the date is) you will get the order so 1 , 2, 3, ... this will be the value that I will be comparing wiht the period and then use that to return the question result:

Be aware that if the patient does not answer one of the surveys the dates from that survey on will be move backwards but you don't give any information about if that happens.

Check PBIX file attach.

[mthomeer]

Wow, Miguel!

You really got me started! One question: a need the "average" of all the answers of all patients, eg at baseline, month 3,... Now I see in the graph the answer per patient in the function of baseline, month 3,...

eg on our plot I expect baseline 4.5 (= average of all answers at baseline of all patients), 3.0 month 3, ...

Michiel

[MFelix]

Hi @mthomeer ,

the formula calculate the average so if you take the patient from the legend you will get all patients average.

Regarding the baseline I'm not sure how you have the data I assumed that the baseline was the first answer from the patient, can you explain how you have the baseline values?

[mthomeer]

Hé Miguel

I use the data of your Patient_period.pbix file, same table

The first answer (=baseline, 01 jan 2020)) of patient A = 5

The first answer (=baseline, 05 feb 2020) of patient B = 4

So the average of all the baseline answers = 4.5

That is what I want to see on the graph.

Michiel

[MFelix · Accepted solution]

Hi @mthomeer ,

Add the following measure:

AVerage of evaluation = AVERAGEX(VALUES('Table'[PatientID]), [Evaluation])

This does the averages of the values per patient of the previous measure:

PBIX file attach.

[mthomeer]

Tx Miguel

[MFelix]

Hi @mthomeer ,

Thank you.