Using Slicer as AND Filter

[Anonymous]

Hi Everyone,

we have some data set as below.

ResponseFeatues

ResponseID	UserID	SurveyId	RecordedDate
987xyz	abc123	qwerty12	01.01.2020
765fsd	def456	qwerty12	01.01.2020
456wqw	abc123	dasdas23	02.01.2020

AnswerFeatures

ResponseID	UserID	SurveyId	QuestionID	AnswerChoice
987xyz	abc123	qwerty12	Q1	A
987xyz	abc123	qwerty12	Q2	A
765fsd	def456	qwerty12	Q2	C
765fsd	def456	qwerty12	Q3	D
456wqw	abc123	dasdas23	Q1	B
456wqw	abc123	dasdas23	Q2	C
456wqw	abc123	dasdas23	Q3	A

Firstly, I want to see which users submit one more survey when I select this survey on slicer. For example, When I select "qwerty12" and "dasdas23" surveys on the slicer, it show to me UserId "abc123". It must not be only 2 selection, It can be 2 more.

And so I want to see how many users submit selected surveys.

[v-diye-msft]

Hi @Anonymous 

Kindly let me know if you'd like to get below results:

Pbix attached.

[Anonymous]

Hi @v-diye-msft ,

I've got this error, when I've tried to open this pbix.

[v-diye-msft]

Hi @Anonymous 

I just removed the enhanced option, please also make sure you're using the latest desktop version, kindly check this one 🙂

[Anonymous]

Hi @v-diye-msft 

I still see same problem. I have downloaded it in there : https://www.microsoft.com/en-US/download/details.aspx?id=56723

Best,

[v-diye-msft]

Hi @Anonymous 

Let me explain the details:

Please add the measure:

Measure = var t = CALCULATETABLE(VALUES(AnswerFeatures[SurveyID]),ALL(AnswerFeatures[SurveyID]))
var a = ALLSELECTED(AnswerFeatures[SurveyID])
Return
IF (
        ISBLANK ( COUNTROWS ( EXCEPT ( a, t ) ) ),
        "Yes")

It will filter the "AND" results from the selection of slicer.

There 2 tables connected by response ID. note that you can't use the SurveyId in ResponseFeature to filter the userID in AnswerFeature.

[Anonymous]

Hi @v-diye-msft 

It seems good but I want to try to see count of userID which submit selected all surveys in Card Visualization, it is not working. Because I can not filter Measure = Yes

[v-diye-msft]

Hi @Anonymous 

You can filter the measure is not blank, or change the below as "1", then filter it =1 :

Measure = var t = CALCULATETABLE(VALUES(AnswerFeatures[SurveyID]),ALL(AnswerFeatures[SurveyID]))
var a = ALLSELECTED(AnswerFeatures[SurveyID])
Return
IF (
        ISBLANK ( COUNTROWS ( EXCEPT ( a, t ) ) ),
        "1")

[Anonymous]

Hi @v-diye-msft 

I can not filter the card visulation by Measure. It doesnt let me to the change filter as you said.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Just got you, kindly add another measure:

Measure 2 = CALCULATE(COUNTROWS(FILTER(VALUES(AnswerFeatures[UserID]),[Measure]="1")))

 Put it into the card.

[Anonymous]

Hi @v-diye-msft 

Thank you. It seems good.

Best,

[Pragati11]

Hi @Anonymous ,

The simplest way to achieve this is:

1. Create a Slicer on your report and move SURVEYID to it.
2. Create a table visual and move USERID column to it.

Now try selecting differebt values from your slicer, it should give you required result.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11,

I'm sorry but it is not working. Because If I select one more SurveyID in the slicer, It shows to me users which submit one or more this surveys. But I need users which submit all selected Survey. I need intersection users for this Surveys.

Best,

[Pragati11]

Hi @Anonymous ,

Can you kindly put some screesnhots of the visual you tried and what is wrong and what is expected?

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

When I select SurveyId="qwerty12" it shows to me 4 UserID:

And If I select SurveyID="dasdas23", I see 2 user:

As you can see, When I select both "dasdas23" and "qwerty12",  UserID must be show onlt "abc123" because it is submit both "dasdas23" and "qwerty12". But now I select "dasdas23" and "qwerty12" it shows to me:

Slicer always works as a "OR" filter, I need "AND" filter

[Pragati11]

Hi @Anonymous ,

First thing slicer doesn't work as an OR, it works as an AND when multiple values are selected.

Second thing, in your previous screesnhot for data tables, I don't see all the users that you have shown in your current screesnhot. 

Am I missing something or you shared data table screesnhots as an example for 2 users only?

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 ,

Yes you can see my sample data table below:

ResponseID	UserID	SurveyID	RecordedDate	Language	Duration	Finished
987xyz	abc123	qwerty12	1.01.2020	EN	100	True
765fsd	def456	qwerty12	1.01.2020	EN	80	True
456wqw	abc123	dasdas23	2.01.2020	AR	92	True
849sda	fds234	qwerty12	2.01.2020	AR	76	False
344kjd	gfds23	dasdas23	2.01.2020	EN	97	True
873jha	ddsa23	ghst562	2.01.2020	EN	75	True
927hsg	dada23	ghst562	3.01.2020	EN	94	False
773dad	54fsd	qwerty12	3.01.2020	EN	73	True

Actually, I need as this solution:

https://medium.com/seismic-data-science/changing-or-to-and-logic-for-power-bi-slicers-1a6b20aee5f5. But it is working very slowly. So I think that I must solve this problem different way.