Filtering between two databases not possible maybe because of modeling

[Anonymous]

 

Hello everyone. I am stuck and could use some help.

I am working with three databases, but for this issue, only two are being considered.

I am referring to the tables in the picture.

The Prepayments and Income table are connected through Contract ID in the Contract (BW) table.

I also have a ‘DateTable’ which is a lookup table containing all the unique dates from Prepayments (Adjustment Date) and Income (Due Date) combined.

I want to get the value (Net Amount (Total)) in my Income Table when the Due Date is one date just before the Adjustment Date. In my example table, for this particular contract ID X, I have my Adjustment Date 01/12/2023.

To the adjustments table, I want to add:

• Two new columns Prepayment Old and Prepayment New. In this example, Prepayment Old would be one date before adjustment → 156 and Prepayment New → 183.
• Two other columns calculating Amount before Adjustment (1716) and Amount after Adjustment (2082)

I tried doing it using

CALCULATE ( MAX ( Prepayments[PrepaymentAdjustment] ), FILTER etc)

But it didn’t work and I think it’s because of the direction of the filtering and the connections.

Any ideas? Thank you.

[123abc]

It seems like you're working with a data modeling issue in Power BI or a similar tool, and you're trying to calculate values based on relationships between tables. The problem might be related to the direction of filtering in your relationships or the way you're trying to apply filters in your DAX (Data Analysis Expressions) formulas.

Here are a few steps you can take to troubleshoot and potentially resolve the issue:

1. Check Relationships:

   • Ensure that the relationships between your tables are correctly set up. In your case, make sure that the relationships between the Contract (BW), Prepayments, and Income tables are established based on the Contract ID.
   • Confirm the direction of filtering in your relationships. It's important to understand which table is the "one" side and which is the "many" side. This could affect how your calculations work.

2. Use RELATED Function:

   • Try using the RELATED function in your DAX formulas to traverse relationships. For example, you might use RELATED(Prepayments[PrepaymentAdjustment]) to get the related value from the Prepayments table based on the Contract ID.

3. Filter Context:

   • Be aware of the filter context in your calculations. The context in which a calculation is performed can affect the results. You might need to adjust your formulas to consider the correct context.

4. Review DAX Formulas:

   • Review your DAX formulas for any syntax errors or logical issues. You might want to break down your formula into smaller parts and test each part independently to identify where the issue might be.

5. Use EARLIER Function:

   • Depending on the complexity of your model and calculations, you might need to use the EARLIER function to refer to a previous row context. This is especially relevant when dealing with calculated columns or table-wide calculations.

Here's a simplified example to get you started:

Prepayment Old =
CALCULATE (
MAX ( Prepayments[PrepaymentAdjustment] ),
FILTER (
ALL ( 'DateTable' ),
'DateTable'[Adjustment Date] = EARLIER ( 'DateTable'[Adjustment Date] ) - 1
)
)

Adjust this formula based on your specific table and column names.

Remember to take a step-by-step approach to diagnose the issue and make incremental changes to your formulas. If the problem persists, providing more details about your data model and DAX formulas can help in offering more specific guidance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly and check the thamps-up.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Hello, That is not it. The two tables that I need to deal with are Income and Prepayments.

I want to get the value (Net Amount (Total)) in my Income Table when the Due Date is one date just before the Adjustment Date. So:

1. I want my Prepayment Old to be 156 from the Income Table when the due date is one date before prepayment adjustment date.

2. Then I want to sum everything in the income table before date of adjustment (found in Prepayments) to find out all the total payments made before adjustment.

I think there is a problem with the data modeling. Is there?