Forum Discussion
Multiple Slicer with AND Logic
Try this:
1. Create calculated table (no relationships):
SalesPersonSlicer = DISTINCT ( salesorder[Sales Person] )2. Create a slicer using SalesPersonSlicer.
3. Create measure:
OrdersDeliveredBySalespersonAndStage = VAR vOrderCounts = ADDCOLUMNS ( VALUES ( salesorder[Order] ), "@RowCount", CALCULATE ( COUNTROWS ( salesorder ) ), "@RowCountSalesPerson", CALCULATE ( COUNTROWS ( salesorder ), TREATAS ( VALUES ( SalesPersonSlicer[Sales Person] ), salesorder[Sales Person] ) ) ) VAR vResult = COUNTROWS ( FILTER ( vOrderCounts, [@RowCount] = [@RowCountSalesPerson] ) ) RETURN vResult
sorry I have been on holiday. Thank you so much, this works, you are a life saver (and time Saver!)
Now comes the next issue. the scope has now changed and people want to selected multiple sales people to compare. the problem arises when in certain cases the selected sales people have worked on the same order.
I will try to exaplain:
If i have the following data
Order
| Order | Stage | Sales Person | Passed |
| Order 1 | Stage 1 | Sales Person 1 | Passed |
| Order 1 | Stage 2 | Sales Person 1 | Passed |
| Order 2 | Stage 1 | Sales Person 1 | Failed |
| Order 2 | Stage 2 | Sales Person 2 | Failed |
| Order 3 | STage 1 | Sales Person 3 | Failed |
| Order 3 | Stage 2 | Sales Person 1 | Passed |
and i have selected Sales person 1 and 2 on the slicer
Presently i get the following results
Sales Person 1 = 2
Sales Person 2 = 1
sales Person 3 = 0
want it should be is
Sales Person 1 = 1
Sales Person 2 = 0
sales Person 3 = 0
i know why as @RowCountSalesPerson will be returning for both sales person 1 and 2 on Order 2 as they are both on the order? but no idea how to deal with this, if indeed I can