Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Madhu155154
Helper I
Helper I

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

 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 

 
4 REPLIES 4
123abc
Community Champion
Community Champion

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
Solution Sage
Solution Sage

@Madhu155154  hi , try it measure 

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

DimaMD_0-1693811119437.png

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

@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
Community Champion
Community Champion

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

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.