Forum Discussion

keball's avatar
keball
Regular Visitor
6 years ago
Solved

Need Help Filtering a Multi-Row Card by Sample Size

Hi -

 

I have a multi-row card with several open ended, unique responses to a behavioral question "How are you doing these days". 

 

I can filter these by demographics and other things and it works fine. 

 

However, I would like to filter it by sample size. So in other words, if there are less than 5 text responses, no text responses show, for confidentiality and sensitivity reasons. 

 

I have tried creating measures and columns that count the number of responses and then filtering that way. I was able to get an accurate count of the unique responses, however when I went to filter the multi-row card , it saw the value as 1 rather than the sum of responses.

 

Thanks for any help. 

 

Kevin

Need Help
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi keball ,

     

    Like this?

    Please refer to my .pbix file.

    Best regards,
    Lionel Chen

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

     

    Best regards,
    Lionel Chen

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

6 Replies

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

    keball , Can you share sample data and sample output in table format?

     

    You need to have measure like this which you can filter at measure level

    calculate(count(Table[Question]),allexcept(Table[personId]))

     =5

    • keball's avatar
      keball
      Regular Visitor
      6 years ago

      Hi - amitchandak  thanks for the quick reply. Some dummy data for this is as follows: 

       

      Unique IDQuestion 1 ResponseGender
      1MaybeM
      2SometimesF
      3I don't knowM
      4 F
      5YesM
      6NoF
      7 M
      8OkF
      9 M

       

      So in this dummy data example - Let's say the number/count of text responses is < 4, then no responses are shown. 

       

      I tried your code above however it is not quite what I am looking for. I have an example in my data where it counts the number of text responses correctly (5), however when I go to filter, it sees the measure variable as binary (1/0) and not as the count. 

      • keball's avatar
        keball
        Regular Visitor
        6 years ago

        amitchandak some extra context...

         

        I used: 

        Q1Count = CALCULATE(DISTINCTCOUNT('Table'[q1_comment]))
         
        To detect proper amount of responses. In example case, it gave me 6.
         
        I then created a measure:
        Q1SampleBINARY = IF([Q1Count]>=6,1,0)
         
        This correctly identified if sample is greater than or equal to 6.
         
        However, when I put "Q1SampleBINARY" as a filter on the Multi-Row Card, it does not filter correctly. I have set it to "Show items when the value is 1", however the card goes blank. Responses only appear if you set to "Show items when the value is 0" and does not filter by sample size correctly. 
         
        Thanks for any help.