Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

AND Filtering in the report

Hi There,

 

I have some table like this:

 

Responses Table:

Answer Table:

 

And the visulations:

My first question is how can I find usercount which has responseID both selected surveyName. So the count of userID must show only 2 (ABC1 and ABC2)

 

And the second question is after I find this 2 user, I want to see their answers distribution for any questiontext in any survey. So I want to click 2 or more surveyname in the slicer, the card must show count of userID entering all selected surveys. And then I select another slicer a survey and question text it show me this users ditribution for this selected questiontext in selected surveyname.

 

Thank you for your helping.

 

Best,

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Anonymous I looked at this, can't really fathom why that is happening. So, might look deeper but I think this fixes the problem, or at least works around it. PBIX is attached.

    Count of Cohort 7c = 
    VAR __tmpTable1 = 
        CALCULATETABLE(
            GENERATE(
                DISTINCT('Table (7)'[UserID]),
                EXCEPT(
                    DISTINCT('Table (7)'[SurveyName]),
                    CALCULATETABLE(DISTINCT('Table (7)'[SurveyName]))
                )
            ),
            REMOVEFILTERS('Table (7)'[AnswerText])
        )
    VAR __tmpTable2 = SUMMARIZE(__tmpTable1,[UserID])
    VAR __tmpTable3 = EXCEPT(DISTINCT('Table (7)'[UserID]),__tmpTable2)
    VAR __CountMax = COUNTROWS(__tmpTable3)

    VAR __Survey = SELECTCOLUMNS('Table (7b)',"__SurveyName",[SurveyName])
    VAR __Question = SELECTCOLUMNS('Table (7a)',"__QuestionText",[QuestionText])
    VAR __tmpTable4 = DISTINCT(SELECTCOLUMNS(FILTER('Table (7)',[SurveyName] IN __Survey && [QuestionText] IN __Question),"UserID",[UserID]))
    VAR __CountMin = COUNTROWS(__tmpTable4)
    VAR __tmpTable5 = FILTER(__tmpTable4,[UserID] IN __tmpTable3)
    VAR __Count = COUNTROWS(__tmpTable5)
RETURN
    SWITCH(TRUE(),
        ISBLANK(COUNTROWS(__tmpTable4)),BLANK(),
        __CountMin < __Count,__CountMin,
        __Count > __CountMax,__CountMax,
        __Count
    )

38 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

    A measure like this

    measure =
    var _sel = maxx(allselected(Response), Response[Survey Name])
    return
    countx(filter(summarize(Response, Response[user_id], "_1", distinctcount(Response[Survey_id])),[_1] =2),[user_id])

     

    All selected surveys are there with user id

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous OK, if you used Cohort for your and slicer, you should have a line like this:

         

        VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2)

        You could have your measure perform the logic of instead of returning countrows to only return whatever calculation you want for values (patients) IN tmpTable3 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Greg_Deckler 

     

    If I add  a new pie chart and I want to see selected users distibution in old pie chart in new pie chart. Is that possible?
    So I want to see this output: