How to use USERELATIONSHIP with multiple Criteria to switch dates, when meeting the Criteria

[Madhu155154]

 Any suggestion on how to achieve the below requirements:

I  have two tables,  one with 4 columns and another one is a calendar table 

Columns -> 

Created Date:

Loan Amount:

Approved date: 

Status: "Pending", " Under-Process", "Approved".

I have created a relationship between the Calender table and the main table :

 * active relation: created date  and Calendar date 

 * inactive relation: approved date and calendar date 

I want to sum up the loan amount when I use the date slicer ( date column from calendar table ) ->

for the status of Approved, it needs to use the Approved date column and for another status it needs to use the Created date 

[123abc]

To achieve your requirements of using the `USERELATIONSHIP` function with multiple criteria to switch dates based on the status column, you can create a DAX measure that sums up the loan amount while considering the relationship between the calendar table and the main table. Here's a step-by-step guide on how to do this:

1. Create a new DAX measure. In Power BI, you can do this by selecting "Modeling" from the top menu and then clicking "New Measure."

2. Use the `SUMX` function to iterate through the rows of your main table and calculate the sum of the loan amount based on your conditions. Here's a sample DAX formula:

```DAX
LoanAmountSum =
SUMX(
MainTable,
IF(
MainTable[Status] = "Approved",
CALCULATE(SUM(MainTable[Loan Amount]), USERELATIONSHIP(MainTable[Approved Date], Calendar[Date])),
CALCULATE(SUM(MainTable[Loan Amount]), USERELATIONSHIP(MainTable[Created Date], Calendar[Date]))
)
)
```

In this formula:

- `SUMX` iterates through each row of the MainTable.
- The `IF` statement checks the status column in each row. If the status is "Approved," it uses the `USERELATIONSHIP` function to establish the inactive relationship with the "Approved Date" and Calendar date. Otherwise, it uses the "Created Date" and Calendar date.
- `CALCULATE(SUM(MainTable[Loan Amount]), ...)` calculates the sum of the loan amount based on the appropriate relationship.

3. Once you've created the measure, you can add it to your visuals, and it will dynamically switch between the "Approved Date" and "Created Date" based on the status.

4. Create a date slicer using the Calendar table's date column. When you use this slicer, the `LoanAmountSum` measure will consider the appropriate date based on the status selected.

By following these steps, you can sum up the loan amount using the `USERELATIONSHIP` function with multiple criteria to switch dates based on the status column in Power BI.

[DimaMD]

@Madhu155154  hi , try it measure 

Amount_ApprovedDate = CALCULATE( SUM('table1'[Loan Amount]), USERELATIONSHIP(dates[Date],table1[Approved date]), 'table1'[Status:] = "Approved" )

[Madhu155154]

@DimaMD , Thank you answering for the question,

But I am getting Static Data, I want it to be dynamic, 

Can't we use userelation  function to switch the dates for specific Cretira 


Test File data userelation.pbix

[ERD]

@Madhu155154 , where are you going to use your measure? 

General approach is to use IF conditions along with CALCULATE and USERELATIONSHIP.