Customer survey only valid answers (horizontal COUNTIF)

[datadonuts]

Hi, i need help with the following issue:

I am using the https://www.kaggle.com/teejmahal20/airline-passenger-satisfaction dataset and try to solve the following problem.

The customers can rank 15 topics from 0 - 5. I guess, 0 stands for "no answer" or "I  didn't use the service". So I decided to leave answers with a 0 out of the calculation and only count the valid answers. The idea is to get

> an average for only answered questions and

> get a threshold, that counts an individual customerfeedback as invalid e.g. if e.g. 50% or less of the questions haven't been answered

I want to calculate as follows: average score = total score* divided by the valid number of ratings, means all given ratings except 0.

*) total score is a calcuated column in Power Query

ID 1 = (5 + 4 + 3 + 5) / 4 valid answers  = 4.25 average rating

ID 3 = (2 + 4 + 0 + 3) / 3 valid answers = 3 average rating

ID 2 and 4 = only 2 valid answers -> invalid feedback (<=50%)

That's a piece of cake in Excel with a horizontal COUNTIF function. But in DAX I cannot figure out a pretty solution.

Here is what I got and it works, but I am quite sure, there are solutions much smarter and more performat too.

Valid Answer =

VAR A = IF(table[columnA] = 0, 1, 0)

VAR B = IF(table[columnB] = 0, 1, 0)

VAR C = IF(table[columnC] = 0, 1, 0)

VAR D = IF(table[columnD] = 0, 1, 0)

RETURN

4 - (A + B + C + D)

I highly appreciate your feedback and wish you peaceful days and a good start into 2021!

[mahoneypat · Accepted solution]

I looked at the Kaggle data and made a simple pbix that may get you started.  Just replace the path in the Source line of the RawData query to point to your local download of the file.  It splits the data into two tables (traveler and survey), which is how you should modle these data IMO).  I also made a DAX column to show if a traveler's response is considered valid or not based on your criteria (all responses were in the test.csv file).  There is also a measure that calculated the average response, excluding responses of 0.

Regards,

Pat

[mahoneypat · Accepted solution]

I looked at the Kaggle data and made a simple pbix that may get you started.  Just replace the path in the Source line of the RawData query to point to your local download of the file.  It splits the data into two tables (traveler and survey), which is how you should modle these data IMO).  I also made a DAX column to show if a traveler's response is considered valid or not based on your criteria (all responses were in the test.csv file).  There is also a measure that calculated the average response, excluding responses of 0.

Regards,

Pat

[datadonuts · Accepted solution]

Thank you for the genious reply. I will check out your channel. 👍

All the best to you for 2021!

[CNENFRNL]

Hi, @datadonuts , as you know, DAX runs on xVelocity in-memory columnar analytic engine, which inherently favors columns of data. Just unpivot columns food, drink etc, then it's ready to be sliced and diced with DAX.

[mahoneypat]

Since you are using a public dataset, it would be easier if you could share your pbix file.  Can you add a link to it on OneDrive, Google Drive, etc.?  If so, a specific expression can be suggested.

Regards,

Pat