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

Visual components supporting AND vs. OR?

Is there a way to pass an AND similar to how we would code that in T-SQL?

Currently, Slicers and Hierarchy visual components combine the results from multiple items selected in case records meet any of the criteria selected.

For example, I have a slicer with "Skills", having DimSkills as a source.

If I select "French" and "German" while having a measure performing a COUNT of employees meeting this criteria, I will get a 17K. This number contains individuals that speaks either French OR German. I want individuals that speak French AND German, which should be around 3K.

Is there a way to achieve the above?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Visual components supporting AND vs. OR?

@igordossantos

 

In this scenario, I assume you have multiple rows for same employee if this employee masters on multiple languages. If so you can't achieve the AND logic in slicer because both "French" and "Germany" in same column. This AND logic is associated with each employee. For your requirement, you need to create a "summarize" table to filter the employees with corresponding languages.

 

Table = SUMMARIZE(FILTER(table7,Table7[Skill]="English" || Table7[Skill]="French"),Table7[Employee],"count of mastered languages",COUNTA(Table7[Skill]))

98.PNG

 

 

Then you can create a COUNT measure like:

Count Of Employees with two languages mastered = CALCULATE(COUNTA('Table'[Employee]),FILTER('Table','Table'[count of mastered languages]=2))

77.PNG

 

 

Regards,

1 REPLY 1
Moderator v-sihou-msft
Moderator

Re: Visual components supporting AND vs. OR?

@igordossantos

 

In this scenario, I assume you have multiple rows for same employee if this employee masters on multiple languages. If so you can't achieve the AND logic in slicer because both "French" and "Germany" in same column. This AND logic is associated with each employee. For your requirement, you need to create a "summarize" table to filter the employees with corresponding languages.

 

Table = SUMMARIZE(FILTER(table7,Table7[Skill]="English" || Table7[Skill]="French"),Table7[Employee],"count of mastered languages",COUNTA(Table7[Skill]))

98.PNG

 

 

Then you can create a COUNT measure like:

Count Of Employees with two languages mastered = CALCULATE(COUNTA('Table'[Employee]),FILTER('Table','Table'[count of mastered languages]=2))

77.PNG

 

 

Regards,