Need Help Filtering a Multi-Row Card by Sample Size

[keball]

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

[amitchandak]

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

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

Unique ID	Question 1 Response	Gender
1	Maybe	M
2	Sometimes	F
3	I don't know	M
4		F
5	Yes	M
6	No	F
7		M
8	Ok	F
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]

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

[keball]

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

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. 

ID	q1	q1_binary	Gender
1	Yes	1	male
2	Perhaps	1	male
3	Ok	1	male
4	No	1	male
5		0	male
6	Sure	1	female
7	Always	1	female
8	Sometimes	1	female
9	Blah	1	female
10	Blooh	1	female
11		0	female

[v-lionel-msft · Accepted solution]

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.

[v-lionel-msft]

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
)

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.