Marrying 3 Tables with Multiple Values and Different Dates

[SneffeL]

Hi, I have been racking my brains for awhile but can't seem to figure out the proper way to go about doing this.

I am trying to figure out how to reference both Table 2 and Table 3 to Table 1.

Using Customer A as an example, the objectives are:

1) Determine whether Customer A is a VIP during the point of visit; and

2) Determine what Customer A's card limit is at the point of visit

In this case, Customer A's VIP status should have lasted from 1/1/2023 to 2/23/2024 and his card limit would be 100 from 1/1/2023 to 2/22/2024 and increased to 200 from 2/23/2024 onwards.

Much appreciated if anyone can provide me with some directions, thank you very much. 

[audreygerred]

Hello! You are going to want to model this as a star schema. Here is an article I wrote about it: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/

You will want to have a customer dimension table where each customer is listed only once. This will join to Tables A, B, and C.

You will want a date dimension table that is also joined to tables A, B, and C. Here is an article I wrote about Date dim tables and the one I love from SQLBI: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/

Once you have these set up you'll be able to make what you need through visuals and measures.

[Bmejia · Accepted solution]

This is based on what I understand, I feel that both T2 and T3 will need additional logic as they either renewal their VIP or there credit limit changes through different periods, but this might get you started.

I am assuming you already joined all the tables on Customer if not do so most likely they be join many to many and both.

Step 1, On Table 3 create a new column and add something like this.  This will give you the last date from the last time the VIP Status was changed (Note that in the return statement I used date 1/1/2050 as the default end date if it the date is blank)

Next Aval Date =

VAR CurrentDate = T3[VIP DATE]

VAR CurrentCustomer = T3[CUSTOMER]

VAR NEXTAVAIL =

CALCULATE (

    MIN ( T3[VIP DATE] ),

    FILTER (

        T3,

        T3[CUSTOMER] = CurrentCustomer

        && T3[VIP DATE] > CurrentDate

    )

)

RETURN

IF(NEXTAVAIL=BLANK(),DATE(2050,1,1),NEXTAVAIL)

It should look something like this:

Step 2 go to T1 and create a new column this will identify if the range is within the VIP Date and New Aval Date (new column) and add the VIP Status

VIP STATUS =

CALCULATE (

    VALUES ( T3[VIP STATUS]),

    FILTER (

        'T3',

        'T1'[VISIT DATE] >=  T3[VIP DATE]  

            && T1[VISIT DATE] <= T3[Next Aval Date]

    )

)

It should look something like this:

Step 3 you would do the same thing as step 1 but on table 2 data

Step 4 you would do the same thing as step 2 only difference is that on “ && T1[VISIT DATE] <=  T2[Next Aval Date]” want to remove the equal sign like this “ && T1[VISIT DATE] < T2[Next Aval Date]” Cause it might create an error if you don’t cause is looking at two similar numeric values.

Then you just use a matrix table and drag the information from T1 to view if customer had VIP Status and what was the card limit during the period.

I hope this get you started.