Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jiayit918871
Frequent Visitor

[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 

 

jiayit918871_0-1636604357318.png

 

Warm Regards,

Bryan

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.