Hi @EltonjohnWick
I can see how frustrating this would be, especially with all the moving parts like slicers, relationships, and the need to dynamically switch date contexts! Let's go through a strategy to approach this in Power BI using USERELATIONSHIP and some conditional filtering to solve your issue with pulling data for a specific week based on the "Drawn" date instead of the default "Lead" date.
1. Understanding Relationships Setup
It sounds like you have:
- A Lending table with multiple date fields (Lead, Approved, Declined, Drawn).
- A Date Hierarchy table with a single date field (let’s call it Date) and a Week Number field (WeekNum).
- One active relationship between Lending's Lead Date and Date Hierarchy's Date column.
- Inactive relationships between the Lending's Approved Date, Declined Date, and Drawn Date with the Date Hierarchy's Date.
This setup is common when you want to analyze different stages (Lead, Approved, Drawn, etc.) using the same Date Hierarchy.
2. Using USERELATIONSHIP in a Calculated Column or Measure
The issue arises because the slicer is taking the active relationship (Lead Date) for filtering, not the Drawn Date. To override this and focus on the Drawn Date, we’ll need to use USERELATIONSHIP within a measure specifically targeting the Drawn Date relationship. Here’s how:
Create a Measure for Filtering Drawn Deals in a Specific Week
Let’s define a measure to filter for drawn deals in a specific week using USERELATIONSHIP to activate the Drawn Date relationship. This way, when you apply the Week slicer, it will reference the Drawn Date instead of the default Lead Date.
DAX
Drawn Deals for Selected Week =
CALCULATE(
COUNTROWS(Lending), -- Or any other aggregation you'd like
USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
)
Display Rows for Drawn Deals for a Specific Week
If you need a table visual to show all rows (not just a count or sum), create a calculated table instead. A calculated table lets you filter the rows based on the Drawn Date relationship and a specific week number:
DAX:
Drawn Deals Table =
FILTER(
ADDCOLUMNS(
Lending,
"WeekNum", RELATED(DateHierarchy[WeekNum])
),
CALCULATE(
USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
)
)
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
