Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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]))
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))
Regards,
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]))
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))
Regards,
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |