Calculate answers per question

[FabvE]

Hi@all,

I have a big dynamic report of Forms surveys which I need to visualize. The table is build like this:

FormID	FormName	Operation	User	Date
ABC	Great Survey	Create	Peter	2021-12-05
ABC	Great Survey	Respond	Alex	2021-12-05
XYZ	New ideas	Create	Bettina	2021-12-14
XYZ	New ideas	Respond	Bettina	2021-12-20
ABC	Great Survey	Respond	Sarah	2022-01-06
XYZ	New ideas	Respond	Claire	2022-01-06
KLM	Old survey	Respond	Sarah	2022-01-26
XYZ	Old survey	Respond	Claire	2022-02-01
KLM	Old survey	Respond	Alex	2022-02-02

Now I need to count the responses per survey (based on FormID not FormName, because names can be duplicate) but only if the survey is listed as created under Operation.

The final visualized table should look like this:

FormName	Answers
Great Survey	2
New ideas	3

KLM should be skipped because there's no Create operation listed.

As bonus there should be a seperate  list with answers, where there's no Create operation for the survey in the whole table:

FormName	Answers
Old Survey	2

(this time KLM)

I already found this post but that didn't help.

Any ideas? 🙂

Best regards

[Anonymous]

Hi @FabvE,

You can try to use the following measure formula if it is suitable for your requirement:

formual =
VAR createidList =
    CALCULATETABLE (
        VALUES ( Table[FormID] ),
        FILTER ( ALLSELECTED ( Table ), [Operation] = Create )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table[FormID] ) ),
        FILTER ( ALLSELECTED ( Table ), [FormID] IN createidList ),
        VALUES ( Table[FormName] )
    )

If the above does not help, can you please explain more detailed information to help us clear about the calculating conditions?

How to Get Your Question Answered Quickly  

Regards,
Xiaoxin Sheng

[FabvE]

No, I only need to count those surveys (FormIDs) which are listed with Create and Response. FormIDs without a create operation should be skipped.

[lbendlin]

" Now I need to count the responses per survey (based on FormID not FormName, because names can be duplicate) but only if the survey is listed as created under Operation." 

This contradicts the sample output where you use Form Name.  Please make a choice.

[FabvE]

Wouldn't it be possible to filter the list by "Operation -> created" and apply the FormID values from this list as a filter for the whole list? Then it should be just a counting of the FormIDs minus 1?

If this is possible, how is it done?

[lbendlin]

It is not clear to me what you are actually trying to achieve.

1. Please provide sanitized sample data that fully covers your issue. 
2. Please show the expected outcome based on the sample data you provided. 

[lbendlin]

"but only if the survey is listed as created under Operation."

Did you mean to write "Respond"  ?