Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
keball
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
amitchandak
Super User
Super User

@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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.