Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
@Madhu155154 hi , try it measure
Amount_ApprovedDate = CALCULATE( SUM('table1'[Loan Amount]), USERELATIONSHIP(dates[Date],table1[Approved date]), 'table1'[Status:] = "Approved" )
@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
@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!