Evaluating record from a fact table baes on subscription period

[george_p]

Hi all,

In my data model, I have a fact table with a list of transactions with client_id, date of transaction and amount. I have also a table with my clients and a table with periods of premium subscriptions. The default plan for my clients is Essential. Each one of them could go premium, then cancel his premium subscription and renew it again at some point later on. I need to assign to each of my transactions a value for the subscription plan under it is made. Below you can find the relationship diagram of my dataset.

Can anyone tell me how to achieve this?

Thanks in advance!

[v-chuncz-msft]

@george_p,

Here is an example for your reference.

https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404

[george_p]

@v-chuncz-msft,

This example is quite different from mine. Maybe I've failed to explain but let me try again.

What I want to achieve is to label each of the records in Fact as "premium" or not. This can be achieved if Fact[date] is between the related Premium[from] and Premium[to]. The problem is that one client could have more than one related records in the Premium table, thus the related records that would be transferred from Premium to Fact could be more than one. I need to evaluate if Fact[date] is between each of the related records and if it is between one of them to label it as "premium" or not.

For instance:

For fact record with date 1/1/2018 and client_id 1, I want to label it as "premium" or "not premium". To check if it is premium, I need to lookup the premium periods for this client from table Premium. However, this client has two premium periods and I need to check 1/1/2018 against the first period and the second period. If it is within one of the periods, it is premium else it is not premium.

Best,

George