CODEWITHSUNDEEP
powerbidata-modelingdata-analysis
Kross
Kross

Reputation: 305

How to analyze data between two fact tables?

I have two fact tables that I want to analyze together, but there is no one direct link between them. I have to use several dimensions in order to relate them. So that I have a more direct example, let's say I have a orders table and a referral table:

These two tables reside in two different areas, which makes it difficult to analyze them together. So far, I've decided that these two tables are my facts. And attempt to relate them using the dimensions they share.

But, I can't guarantee that each order has only one referral or vice-versa. The rule that I need to apply is to find the first order item between the referral date and the expiration date, where all other dimensions match. But I also have to take into account that if the order has already been assigned to another referral, it should take the next available order item, if possible.

What I want to ultimately calculate is the age of a referral, or the age to date, in the case that no order is found.

I can see the way to do this in Python, or C#. But is it possible to do this in DAX using a measure or a calculated field? Does it make sense to do so? If I were to calculate this ahead of time in an earlier Python script for example, do I combine my facts into a single table?

Upvotes: 0

Views: 307

Answers (1)

CR7SMS
CR7SMS

Reputation: 2584

It is definitely possible to do this in Power BI DAX. But, I would say its easier to do this in the data model rather than in DAX. If you are well-versed with Python scripts, you can use it to create a single fact table. This would make all your further calculations and measures much more simpler to create.

If you still want to use DAX for this, use the following steps:

Step 1: Create a relationship between Orders and Referral based on Customer ID and SalesRepID (You would have to concatenate the fields to create the relationship)

Step 2: Then you would have to create a filter column which checks if the order date is between the referral date and expiration date. Something like:

 FILTER_FLAG = IF(Orders[OrderDate]>=RELATED(Referral[ReferralDate]) &&
                  Orders[OrderDate]<=RELATED(Referral[ExpirationDate]),1,0)

Step 3: Now you would have to create a rank variable to select the minimum order date for each Order Item. You should also make sure to filter for FILTER_FLAG=1 in the calculation. The following link should help in achieving that:

https://community.powerbi.com/t5/Desktop/Summarize-and-Rank-by-multiple-columns-and-rows/td-p/330108

Once this is created, you can simply use the records with Rank=1 for your analysis. As mentioned before, it can be a little complicated to achieve this, so I would suggest using the Python scripts to create a single table before hand. Hope this helps.

Upvotes: 1

Related Questions