topic Re: Userelationship for multiple dates in DAX Commands and Tips

Userelationship for multiple dates

EltonjohnWick — Wed, 13 Nov 2024 21:59:52 GMT

I have this dax that works for filtering lending to drawn on a table.

Drawn Deals =
CALCULATE(
COUNTROWS(Lending), -- Or any other aggregation you'd like
USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
)

What I need is a combined code that also looks at lead, approved and declined. These stages have a slicer to flip between them, which I need to trigger to the lookup on the relevant date ie if column stage slicer is lead then look at lead date, if slicer is approved look at approved date. The slicer is a week output but it's linked to date table to pull the week though. Lead is the active relationship to the date table while the others are inactive linked to the date table.

Thanks

Re: Userelationship for multiple dates

Sahir_Maharaj — Wed, 13 Nov 2024 23:12:54 GMT

Hello EltonjohnWick,

Can you please try the following approach:

Drawn Deals = VAR SelectedStage = SELECTEDVALUE(StageSlicer[Stage]) RETURN SWITCH( SelectedStage, "Lead", CALCULATE( COUNTROWS(Lending), DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum]) ), "Approved", CALCULATE( COUNTROWS(Lending), USERELATIONSHIP(Lending[Approved Date], DateHierarchy[Date]), DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum]) ), "Declined", CALCULATE( COUNTROWS(Lending), USERELATIONSHIP(Lending[Declined Date], DateHierarchy[Date]), DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum]) ), "Drawn", CALCULATE( COUNTROWS(Lending), USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]), DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum]) ), BLANK() )

Re: Userelationship for multiple dates

EltonjohnWick — Thu, 14 Nov 2024 08:28:34 GMT

Thanks for sending on, however this didn't work. I can see the stage options available for the relevant week but the table is blank when I add the measure

Re: Userelationship for multiple dates

johnt75 — Thu, 14 Nov 2024 10:30:30 GMT

This seems like a perfect use case for a calculation group. Create a base measure as

Num deals = COUNTROWS( Lending )

and then create a calculation group with items like

Lead Deals = SELECTEDMEASURE() Drawn Deals = CALCULATE ( SELECTEDMEASURE(), -- Or any other aggregation you'd like USERELATIONSHIP ( Lending[Drawn Date], DateHierarchy[Date] ) )

You don't need to manipulate the relationship for the Lead calculation item because that relationship is already active. And you don't need the SELECTEDVALUE as that filter is already being applied.

Re: Userelationship for multiple dates

Anonymous — Fri, 15 Nov 2024 06:10:24 GMT

Hi EltonjohnWick ,

You need create stage column as table slicer and then try to create formula like below:

Drawn Deals1 = VAR SelectedStage = SELECTEDVALUE ( StageSlicer[Stage] ) RETURN SWITCH ( TRUE (), SelectedStage = "Lead", CALCULATE ( COUNTROWS ( Lending ), TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Lead Date] ), DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] ) ), SelectedStage = "Approved", CALCULATE ( COUNTROWS ( Lending ), TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Approved Date] ), DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] ) ), SelectedStage = "Declined", CALCULATE ( COUNTROWS ( Lending ), TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Declined Date] ), DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] ) ), CALCULATE ( COUNTROWS ( Lending ), TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Drawn Date] ), DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] ) ) )

Best Regards,
Adamk Kong

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

Re: Userelationship for multiple dates

EltonjohnWick — Wed, 20 Nov 2024 08:37:04 GMT

Thank you so much.

Finally works and saves me the visual clutter of having two different date slicer.

Much appreciated