cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

1 ACCEPTED SOLUTION

Hi @keball ,

 

Like this?

Please refer to my .pbix file.

v-lionel-msft_2-1597657067315.png

v-lionel-msft_3-1597657086734.png

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.

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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. 

@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.
 

 

 

Hi @v-lionel-msft @amitchandak -

 

 

I have a variable which counts the number of responses correctly (q1_binary) and an indicator of if the number of responses is greater than 5 (Countbin).

 

keball_0-1597067193722.png

 

However, when I apply filter "Countbin is 1", it does not filter correctly. It sees all responses as a value of 0. You can test this by clicking male (4 responses) vs. female (5 responses). 

 

Please do you have any idea how to fix this? I just want it to not show comments if count < 5. This works for figures but not for table/matrix/multi-row card. 

 

IDq1q1_binaryGender
1Yes1male
2Perhaps1male
3Ok1male
4No1male
5 0male
6Sure1female
7Always1female
8Sometimes1female
9Blah1female
10Blooh1female
11 0female

Hi @keball ,

 

Like this?

Please refer to my .pbix file.

v-lionel-msft_2-1597657067315.png

v-lionel-msft_3-1597657086734.png

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.

View solution in original post

Hi @keball ,

 

Or liek this?

Text length = 
VAR x = LEN( MAX(Sheet7[Question 1 Response]) ) 
RETURN
IF(
    x = BLANK(),
    0, x
)
Filter = 
IF(
    [Text length] >=6,
    1, 0
)

v-lionel-msft_0-1597048309600.png

 

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.

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors