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
igordossantos
Helper I
Helper I

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
v-sihou-msft
Employee
Employee

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

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

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

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.