Userelationship for multiple dates

[EltonjohnWick]

Hi 

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 

[EltonjohnWick]

Thank you so much. 

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

Much appreciated

[v-kongfanf-msft · Accepted solution]

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.

[johnt75]

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.

[Sahir_Maharaj]

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()
    )

[EltonjohnWick]

Hi 

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