topic Re: How to use USERELATIONSHIP with multiple Criteria to switch dates, when meeting the Criteria in DAX Commands and Tips

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

Madhu155154 — Mon, 04 Sep 2023 06:27:32 GMT

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

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

ERD — Mon, 04 Sep 2023 06:47:13 GMT

Madhu155154 , where are you going to use your measure?

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

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

DimaMD — Mon, 04 Sep 2023 07:05:39 GMT

Madhu155154 hi , try it measure

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

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

Madhu155154 — Mon, 04 Sep 2023 09:01:19 GMT

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

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

123abc — Mon, 04 Sep 2023 09:13:27 GMT

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.