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
ivancespedesl
Helper I
Helper I

Custom Lookup - last customer segment type to products table via Customer Code

Hi all,

 

I have two tables: Customers and Products. The Customers data is a compilation of transversal cuts, so I have the same client everyday. In the products table, I have historic data from products they have purchased. Both have the client and product with their specific customer id, but only the Customers table has segment type. This segment can vary over time: Premium, Classic, etc.

 

My inquiry is that I want the Products table, referring to the customer code, to lookup for the LAST segment that the customer was assigned (today). I was thinking about creating a custom table or something related, but I dont know what would be the best approach.

 

 

Thanks in advance,

 

IC

1 ACCEPTED SOLUTION

@ivancespedesl 

This particular problem is often referred to as a slowly changing dimension. There are several ways to solve this but my preferred method comes from an old friend of mine. check out this link and see if it provides the necessary guidance for you.

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

@ivancespedesl ,

 

Could you please share some sample data and give the expected result?

 

Regards,

Jimmy Tao

I Have the following table 'Customers':

Codigo_ClienteTipo_De_PersonaCodigo_OficialCodigo_IngresoMonthFechaCargaEs_Cliente
442222NLMIESESClasicoJune2019-06-28S
442222NLMIESESClasicoMay2019-05-31S
442222NLMIESESPreferencialApril2019-04-30S
442223NCTEJADAClasicoJune2019-06-28S
442223NCTEJADAClasicoMay2019-05-31S
442223NCTEJADAPrestigeApril2019-04-30S
442224NFRSANTANAClasicoApril2019-04-30S
442224NEAVILAClasicoMay2019-05-31S
442224NEAVILAPreferencialJune2019-06-28S

 

And the following table 'Products':

CODIGO_CLIENTENOMBRE_PRODUCTONUMERO_PRODUCTO
442222CUENTAS DE AHORROS NÓMINA ELECTRÓNICA DOP11305000008088
442223VISA CLASICA668060
442224CUENTAS DE AHORROS CON LIBRETA USD21072030006329
442225VISA ORO498904

 

What I need is to get this:

CODIGO_CLIENTENOMBRE_PRODUCTONUMERO_PRODUCTOSegmento
442222CUENTAS DE AHORROS NÓMINA ELECTRÓNICA DOP11305000008088Clasico
442223VISA CLASICA668060Clasico
442224CUENTAS DE AHORROS CON LIBRETA USD21072030006329Clasico

 

How? Selecting the last (based on date closest to today) Codigo_Ingreso from the Customers table and doing a lookup on the Products table.

 

Please help me with this matter.

@ivancespedesl 

This particular problem is often referred to as a slowly changing dimension. There are several ways to solve this but my preferred method comes from an old friend of mine. check out this link and see if it provides the necessary guidance for you.

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, @KC @v-yuta-msft 

 

I have decided to do the snowflake version of this link:

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/

 

My approach would be to filter using MAX date of the report, however, what if a customer stops appearing on the MAX date because he is out of the company? I should have MAX date per customer in order to maintain all the Customer ID. Also, I should use an SQL query to create the table. 

 

Any suggestions?

Thanks for the help but I didnt understand the explanation in the link. I got lost about the surrogate ID. I will keep trying to understand.

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.