Userelationship issue pulling back wrong date column

[EltonjohnWick]

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. 

• Totally lost now and any help would be appreciated. At my wits end why the userelationship won't switch to the other stage dates 

[Poojara_D12]

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:

1. 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

  1. Place the Drawn Deals for Selected Week measure in a card or KPI visual to show the count (or sum) of drawn deals for the selected week.
  2. Use the Drawn Deals Table calculated table in a table visual to display individual rows for all drawn deals in the selected week.
  3. Important: Make sure your Week slicer is connected to the DateHierarchy table and not directly to the Lending table. This will allow it to interact properly with the USERELATIONSHIP function.                             . Verify Date Format Consistency and Relationships

     Since you mentioned date formatting, ensure that:

     • The Drawn Date and other date columns in the Lending table are of the same data type (Date or DateTime) and format as the Date column in the DateHierarchy table.
     • Your relationships are correctly set up in the Manage Relationships view, with only the Lead Date relationship set as active.

     4. Testing Other Scenarios

     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.

     ---

     Final Thoughts

     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

[EltonjohnWick]

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 

• Approved stage for week 40 had 2 deals within that range. I want those 2 business names to pull through just like I can now with the standalone drawn dax you supplier 

[v-zhouwen-msft]

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

[KPR106]

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.