Forum Discussion
Filtering by SELECTEDVALUE and dynamic slicer
Hello everyone. I'm trying to find new customer by sales reps, however I'm getting incorrect results.
I have uploaded the sample file here to work here.
New customers BI problem.pbix
Here is the snapshot of my relationships
Inside rep shows the table I have made for slicer. It updates and add new reps dynamically.
I want to get new customers based on comparison of current month's customers with customers in the past 11 months.
However I'm not getting it right, as can be seen in the following image. It is showing customer 1 as new customer in January when it shouldn't.
It can be seen from the data that customer 1 had sales in November also. So, it shouldn't be counted as new customer.
| Document no. | Inside rep | Customer | sales amount | document date |
| 22001 | No rep | customer 2 | 340 | 10/11/2022 |
| 22002 | No rep | customer 1 | 20 | 20/11/2022 |
| 22003 | No rep | customer 1 | 20 | 20/12/2022 |
| 22004 | No rep | customer 3 | 12 | 20/12/2022 |
| 22005 | No rep | customer 2 | 32 | 21/12/2022 |
| 22006 | No rep | customer 4 | 12 | 22/12/2022 |
| 22007 | No rep | customer 5 | 33 | 23/12/2022 |
| 22008 | No rep | customer 5 | 22 | 24/12/2022 |
| 22009 | No rep | customer 4 | 12 | 25/12/2022 |
| 22010 | rep 1 | customer 1 | 20 | 05/01/2023 |
| 22011 | rep 2 | customer 2 | 20 | 06/01/2023 |
It seems due to SELECTEDVALUE, my slicers filters all data based on inside rep and run the rest of the measure. Hence, all the sales with "No rep" get excluded. Hence it counts "No rep" older customers, as new customers for reps. So, if there is a change in inside reps for a customer (from "No rep" to "rep 1"), it considers that customer new, which is not right.
Here is my measure for New customer sales:
New customer sales =
var _selectedInsiderep = SELECTEDVALUE('Inside reps'[Inside rep])
var Priorcustomers =
CALCULATETABLE(
DISTINCT('Customer number'[Customer no.]),
DATESBETWEEN(
'datetime'[Date],
EDATE(MIN(datetime[Date]),-12),
MIN('datetime'[Date]) - 1
)
)
var CustomerSM =
CALCULATETABLE(
DISTINCT('Customer number'[Customer no.]),
DATESBETWEEN(
'datetime'[Date],
BLANK(),
MAX('datetime'[Date])
),
FILTER(
Sales,Sales[Inside rep] = _selectedInsiderep
)
)
RETURN
CALCULATE([Total sales amount],except(CustomerSM,Priorcustomers))
I can't hard-code each inside rep, since their number are increasing. That's why I use slicer: getting Inside reps values dynamically from sales data. However, it filters visual and causes problem in the measure.
In my case what has worked is disconnecting slicer table with the fact table. As I converted this Inside rep table into a disconnected table, it stopped messing up with my results.
I got my intended results.
1 Reply
- shaharyaramjadFrequent Visitor
In my case what has worked is disconnecting slicer table with the fact table. As I converted this Inside rep table into a disconnected table, it stopped messing up with my results.
I got my intended results.