Find value based on multiple keys in different tables

[Anonymous]

Hi,

Let me explain a few things first:

Activities 

Activities are groups of products which a client can use. Each activity has its own activityObjectID

Activity 1 (Activityobjectid 1)

Activity 2 (Activityobjectid 2)

Activity 3 (Activityobjectid 3)

Activity 4 (Activityobjectid 4)

Activity 5 (Activityobjectid 5)

ActivityLogs

This is a table were we see each client (ClientID) and the activity they used, including dates.

Client 1 - Activity 1

Client 2 - Activity 1

Client 3 - Activity 1

Products_activities

Activities translate into products which results in a price of a activity.

But the same activity(activityobjectID) can result in a different product based on the client contract. 

Product 1 (Productobjectid 1 ) - Activity 1 

Product 2 (Productobjectid 2 ) - Activity 1 

Product 3 (Productobjectid 3 ) - Activity 1  

Product 4 (Productobjectid 4 ) - Activity 1 

Product 5 (Productobjectid 5 ) - Activity 1 

Contracts

Each client has a contract(contractID)

In the contract table we have a colum that provides the productID that can be billed depending on the activitity they used.

Client 1 - ProductID 5

Client 2 - ProductID 2

Client 3 - ProductID 1

Client 4 - ProductID 4

Client 5 - ProductID 5

I want to add a new colum to activitiy_logs with the productID on each row of the activity a certain client used.

Client 1 - Activity 1 - Product 5

Client 2 - Activity 1 - Product 2

Client 3 - Activity 1 - Product 1

The activities_log has to check the contract table based on clientID to find productIDs that can be billed. It also have to check the date of the contract because client could have multiple rows of contracts from the past.

Then these productsIDs have to be compared with the product_activity table to check which activities can be matched to this ProductID

The result of matched activities must then be checked to the activity_logs (activityIDS),  IF TRUE then add the product ID to the new column in the  Activitylog table

Maybe there is a better way to accomplish this, but i see no other way.. 

Any suggestions how to do this?

Thanks

Casper

[Anonymous]

Hi @Anonymous ,

Data model looks complicate, please share some sample data and expected result.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Best Regards,

Jay

[Anonymous]

Hi Jay,

I will try to make a example pbix. My current pbix has over 700 tables and has alot of confidential information.

In the mean time, is there a possibilty to create a group/list of all activities that a certain product(id)  which i could filter on?

I got pretty far in matching products to activities but with my filter it only show a min/max of a found result.. which results in alot of blanks.. 

I would like to create a list/group that says:

Product 1 = could be used on activity 1/4/6/7/8/9 etc  which i could filter on...

This way i could match a client ID with the contract. Find the productID from the contract that could be billed and match the related activities to a product ID with activities in the activityLOG.

Product 1 - activitiy 1
product 1 - activitiy 4

product 1 - activitiy 6

product 1 - activitiy 7

product 1 - activitiy 8

product 1 - activitiy 9

Activity LOG

Filter ClientID from activity Log with Client ID from Contract
Filter ProductID from Contract = with Group/List of activities that match that ProductID
Filter Activity from activity Log with Found results of Group/List Activities.  IF match is found, show ProductID from contract.

Cheers

Casper