Conditional Measure

[Anonymous]

Hi,

I'm working with data set containing survey results. Instead a single row containing a user’s response to each question within the survey, each question has its own row for each user (example below). While measures and conditional columns have been created for simple stats, the structure is making it difficult, for me at least, to create a measure where the Answer from two or more questions, associated with a given user, meet a defined condition.

Example:

Using the table below as a reference, I need a measure that will count the number of users who have answered ‘No’ to ‘Have you attended before?’ but have then answered ‘Yes’ to ‘Did you attend this time?’ Based on the table below, the measure would return a result of '2' users.

The purpose is to track how many users have not attended a previous event but, as the result of a marketing campaign, have chosen to attend the current event.

I’d normally use a simple Advanced Filter option to set an AND condition but as the Answers aren’t contained within the single row, I need to associate multiple responses/rows (presumably by UserID) to different questions. Hope that makes sense.

Any help would be appreciated. Thanks

UserID	Name	Question	Answer
1212	Paul Smith	Had you attended before?	No
1212	Paul Smith	Did you attend this time?	Yes
1251	Julie Jones	Had you attended before?	Yes
1251	Julie Jones	Did you attend this time?	No
1271	Kelly Kid	Had you attended before?	No
1271	Kelly Kid	Did you attend this time?	Yes

[v-yulgu-msft]

Hi @Anonymous,

Based on my test, the solution provided by Zubair_Muhammad can return correct result. Please see below screenshot. 

Have you worked it out? If it doesn't meet your request, what is your desired output? Please post an image to illustrate your requirement.

Best regards,
Yuliana Gu

[Zubair_Muhammad]

Hi @Anonymous

Please see the file attached here as well.

You can run these Formulas with a bigger data set to test

[Zubair_Muhammad]

Hi @Anonymous

Please try this measure

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            TableName,
            TableName[Name],
            "RESULT", IF (
                AND (
                    CALCULATE (
                        VALUES ( TableName[Answer] ),
                        FILTER ( TableName, TableName[Question] = "Did you attend this time?" )
                    )
                        = "YES",
                    CALCULATE (
                        VALUES ( TableName[Answer] ),
                        FILTER ( TableName, TableName[Question] = "Had you attended before?" )
                    )
                        = "NO"
                ),
                "Success",
                "Nope"
            )
        ),
        [RESULT] = "Success"
    )
)

[Anonymous]

Hi Zubair,

Thanks for your reply. I'm not sure that measure is returning a count of when both conditions have been met. At least that's how I'm reading it. 

[Zubair_Muhammad]

Hi @Anonymous

Also you can get a table of names of those persons who met these conditions

Go to Modelling Tab and select NEW TABLE

New Table =
FILTER (
    SUMMARIZE (
        TableName,
        TableName[Name],
        "RESULT", IF (
            AND (
                CALCULATE (
                    VALUES ( TableName[Answer] ),
                    FILTER ( TableName, TableName[Question] = "Did you attend this time?" )
                )
                    = "YES",
                CALCULATE (
                    VALUES ( TableName[Answer] ),
                    FILTER ( TableName, TableName[Question] = "Had you attended before?" )
                )
                    = "NO"
            ),
            "Success",
            "Nope"
        )
    ),
    [RESULT] = "Success"
)

[parry2k]

If I understood correctly, you want to know unique employee who attended before and/or this time

add a simple measure 

Attended Answer = Calculate(DistinctCount(Survey[Name]), Filter(Survey, Survey[Answer] = "Yes"))

In table add question and this new measure, you will get the count, add slicer on the question to see the count of question you are interested in