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
    )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous Right, so use the same Cohort measure syntax in a new measure but change the RETURN statement to something like:

 

RETURN
  IF(MAX([UserID]) IN __TmpTable3,[Old Measure for Value you want to display in pie chart],BLANK())

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Greg_Deckler What is that mean "Old Measure for Value you want to display in pie chart" I didnt understand?

 

I did it like this but again It show wrong data for me. q3a2text must be 2 in the pie chart.

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous What was the value you were displaying in your pie chart originally?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Greg_Deckler you can see my previous message, q3a2text is always shown 3 even if I run your sent this code:

      RETURN
  IF(MAX([UserID]) IN TmpTable3,[Count of Cohort],BLANK())

      But I need q3a2text must be shown 2 because 2 userID answer the question as q3a2text and at the same time they join this two survey.  

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Greg_Deckler  do you have any solution for this?

        Best,