Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi
Been trying to solve this for over 4hrs...
I have 2 table. 1 lending and the the other is a date hierarchy.
The table that I'm pulling in multiple single row data, based on the lending table. Ie tm, colleague business name, product and value. Eg I want to see all timmy's drawn deals for week 44.
There is 1 active relationship which is the lead date with date on the date hierarchy and the other (approved, declined and drawn are inactive.
I have associated slicers to check the relevant criteria. The issue is the week slicer. If I wanted to pull back drawn in week 44 it takes the lead date instead of drawn which is rightly so.
I have ran about 8 different userelationship dax examples from chat gbt which is usually spot on to overwrite the lead week with the date from the stage I choose, in this example I want it to be the drawn date. I have drawn dates, I have week numbers associated to that drawn date and no matter what I do its always the lead date it pulls. I have cards on my dashboard that use userelationship and they work fine but that's more of suming up and showing totals that actuals rows of data. Formatting is all matched for date short for all relevant fields.
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.
It sounds like you have:
This setup is common when you want to analyze different stages (Lead, Approved, Drawn, etc.) using the same Date Hierarchy.
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])
)
)
Using the Measure and Calculated Table in Your Report
Since you mentioned date formatting, ensure that:
If you also want to filter by other dates (Approved, Declined, etc.), create separate measures using the same pattern but replace Drawn Date with the relevant date column and adjust the inactive relationship in each measure.
This approach isolates the Drawn Date relationship only within specific measures or calculated tables. When combined with the Week slicer, these measures should now respect the chosen week for Drawn deals, rather than reverting to the default Lead Date relationship.
Let me know if this aligns with what you were aiming for, or if you encounter any issues!
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
Thank you so much. The table finally pulls through the correct business details. 2 instead of 1.
What I need for it to work end to end is for the table to dynamically charge based on the stage filter along with the week filter that now works.
Eg. The stages on the filter are lead, approved, fulfilled, sanctioned, drawn, ntu and declined. When I select a stage I want the relevant businesses to pull through. Ie lead stage will only have the lead week via the lead date business details , approved stage will approved week via approved date and so on.
Eg
Hi @EltonjohnWick ,
Can you provide a .pbix file without sensitive data? It's a bit difficult for me to understand based on your description.
If you are unsure how to upload data please refer to
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou
Hello @EltonjohnWick
Please go through the following steps to resolve the issue:
Ensure that your inactive relationships are correctly defined between the Drawn date and the Date Hierarchy table.
Make sure there is a proper interaction of slicers with your DAX measure, follow these steps:
Slicer Configuration: Ensure that slicers for selecting weeks, products, and stages target the Date Hierarchy table, not the Lending table.
Context Management: Make sure the slicers do not inadvertently override the context for "Drawn" dates. Filtering: If needed, add an additional layer of filtering to focus on only the "Drawn" dates.
Use the DAX formula for additional layer of filtering
Drawn Deals Value (Week 44) =
CALCULATE (
SUM('Lending'[Value]),
USERELATIONSHIP('Lending'[DrawnDate], 'Date'[Date]),
'Date'[WeekNumber] = 44,
'Lending'[Stage] = "Drawn"
)
Debugging the DAX expression
To troubleshoot, use the ALL function to clear filters and ensure you're not inadvertently being filtered by the "Lead" date column:
Drawn Deals Value (Week 44) =
CALCULATE (
SUM('Lending'[Value]),
USERELATIONSHIP('Lending'[DrawnDate], 'Date'[Date]),
'Date'[WeekNumber] = 44,
ALL('Lending'[LeadDate]) -- Clear any possible filters from LeadDate
)
In the end test the measure with different weeks and stages to ensure the USEREALTIONSHIP is being applied correctly.
If this is helpful for you, Please let me know.Thank You.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
36 | |
30 | |
18 | |
13 | |
8 |
User | Count |
---|---|
50 | |
36 | |
30 | |
15 | |
12 |