Cross filter from same table

[Shivani_06]

Problem with cross-filtering the same table in report.

‎

Hello, I am working with questionnaires, so I have a table user_question table that looks like this:

And User table

Relationship in between both table is one to many and both directional to filter it both ways.

It can have like 572 questions.

And the report looks like this:

What I need is that when I select any answer in question B other visual should show me count of same users who have given answer for the Question C also, so for above example if I select dog in question B then it should show call=1 and put=1 because those same users 2 & 3 answered for question C as well.

Simply, I want count of users who have given answer to question B and also answer to question C. so it should cross-filter.

But if I try currently its going blank in my case, answer name and question name is pulled from same table user question table and both visuals has visual level filter for question.

And I need to plot more than 2 visual on same page to analyze question level user data.

Thank you!!!!

[v-xuxinyi-msft]

Hi @Shivani_06 

Based on your description, I am afraid that your situation can not be fully realized, the following methods are for your reference:

1. create a table as the slicer

Question_name = VALUES(user_question[question_name])

2. create several measures as follow

User_count = CALCULATE(DISTINCTCOUNT(user_question[user_id]), user_question[question_name] in VALUES(Question_name[question_name]))

Related_count = 
VAR a =
    CALCULATETABLE (
        DISTINCT ( user_question[user_id] ),
        ALLSELECTED ( user_question ),
        user_question[question_name] IN VALUES ( Question_name[question_name] )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( user_question[user_id] ),
        ALLSELECTED ( user_question ),
        user_question[answer_name] IN VALUES ( user_question[answer_name] ),
        user_question[user_id] IN a,
        user_question[question_name] IN VALUES ( user_question[question_name] )
    )
RETURN
    b

Best Regards,
Yulia Xu

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

[Shivani_06]

Hi @v-xuxinyi-msft , 

Thank you so much for your feedback. 

I am looking for cross filtering solution which I have mentioned above but in this case if I uncheck slicer my 1st visual will give me all users who have given answer to all questions but I wanted to see actual count of user for particular question at first and also I want to see actual count of users in 2nd visual as well that is not possible without uncheck slicer. 

And I want to see related count only if I select any answer from 1st visual so that it crossfilter and give me the count of user who have answered to other question as well in 2nd visual. 

And If I have not selected any answer on visual then it must show user count for particular question for each visuals with there respective question name flter.

I can't have any slicer on page but can have visual level question filter. Also I may have to compare two or more questions visuals on same page.

Regards,

Shivani_06