Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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 bot...
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
    )
amitchandak's avatar
amitchandak
Super User
5 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak and Greg_Deckler 

     

    Thank you for your responses but my main problem is:

     

    Can you help me?

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 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

        Greg_Deckler I have used this code in Cohort:

         

        But In my report:

        When I select other SurveyName and Questiontext in there, It shows all entered this survey. But I want to show only this 2 users count in the pie chart.

         

         

         

         

         

         

         

         

         

         

         

        So, In my report: