Forum Discussion

jiayit918871's avatar
jiayit918871
Frequent Visitor
4 years ago

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

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

Need Help

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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