Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I wonder if anyone can help, i have been going around in circles with this one for days.
I will try to explain as best as i can
this is an example data table i have
i want to build a slicer on the sales person and the stage, however if i select multiple stages on the stage slicer i want to only return data when the sales person is the only sales person on the selected stages on the order.
For example if i selected sales person 10 on the sales person slicer and stages 1 and 2 on the stage slicer it would return 1 as he was the sales person in both stage 1 and 2 on sales order 01F0AFDD-DAD8-EB11-BACB-0022484232D1 (Highlighted) However if i selected stages 1, 2 and 3 on the stage slicer it would return 0 as although he was the sales person on stage 1 and 2 he was not the sales person on stage 3
I hope i have explain that well enough
i have the following but it does not work as intended:
Solved! Go to Solution.
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
Proud to be a Super User!
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
thanks so much for your reply. not sure this will achieve what i am looking for. I also need to filter by sales person. basically i am trying to work as an example is if i selected stage 1 and 2, how many orders individual sales people have done, when they were the sales person on both these stages. if they are not the sales person on both stages, this data is exclude. Form the highlight data if i selcted 1 and 2, it would retunr 1 if i had obviously on a normal slicer it will return if they were the sales person on either of these stages
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
Proud to be a Super User!
Try this measure:
OrdersDeliveredBySalespersonAndStage =
IF ( DISTINCTCOUNT(salesorder[Sales Person]) > 1, 0, 1 )
-----
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |