Forum Discussion

shaharyaramjad's avatar
shaharyaramjad
Frequent Visitor
3 years ago
Solved

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 repCustomer sales amountdocument date
22001No repcustomer 234010/11/2022
22002No repcustomer 12020/11/2022
22003No repcustomer 12020/12/2022
22004No repcustomer 31220/12/2022
22005No repcustomer 23221/12/2022
22006No repcustomer 41222/12/2022
22007No repcustomer 53323/12/2022
22008No rep customer 52224/12/2022
22009No repcustomer 41225/12/2022
22010rep 1customer 12005/01/2023
22011rep 2customer 22006/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

  • 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.