cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ivancespedesl Regular Visitor
Regular Visitor

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

Accepted Solutions
kcantor Super Contributor
Super Contributor

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

@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 Datanaut!




View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

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

@ivancespedesl ,

 

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

 

Regards,

Jimmy Tao

ivancespedesl Regular Visitor
Regular Visitor

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

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.

kcantor Super Contributor
Super Contributor

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

@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 Datanaut!




View solution in original post

ivancespedesl Regular Visitor
Regular Visitor

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

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.

ivancespedesl Regular Visitor
Regular Visitor

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

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?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)