[Need Help]: How to join a table with Slowly Changing Dimension in Power BI

[jiayit918871]

Hi,

I have a category benchmark table below with SCD design with start date and end date. I need to categories my salesperson based on their sales value with the sales benchmark below. How do I deal with that?

Below is the link to get the data, PBIX file and Expected Result. Thanks.
https://drive.google.com/drive/folders/1PKxQ9899BrvmEQ0odZjNRf2a9M7LtxRN?usp=sharing 

Warm Regards,

Bryan

[Ashish_Mathur]

Hi,

Dynamic segmentation works with a diconnected table.  A diconnected table ideally has only the first 3 columns which you hvae shown in your image.  However the complication crops in with the date in the last 2 columns.  Since there will be a Date column in your sales data as well, that will have a relationship with this table to know which bucket the salesperson falls in.

By the way, what is SCD?

[amitchandak]

@jiayit918871 , For SCD you can refer

Do not join category and sales person , try like

Measure =
var _sales = calculate(Sum(Salesperson[sales]))
return
calculate( sumx(filter(values(Salesperson[Salesperson]),_sales >= Min(Category[Category Value (Min)]) && _sales <= Max(Category[Category Value (Min)]) ), _sales),
filter(Salesperson, Salesperson[date] >= Min(Category[Start Date]) && Salesperson[date] <= Min(Category[end Date])))

Or refer

https://www.youtube.com/watch?v=tKeaQpWynzg