Forum Discussion

EltonjohnWick's avatar
EltonjohnWick
Frequent Visitor
1 year ago
Solved

Userelationship for multiple dates

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 

 

DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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's avatar
      EltonjohnWick
      Frequent Visitor
      1 year ago

      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 

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • EltonjohnWick's avatar
    EltonjohnWick
    Frequent Visitor
    1 year ago

    Thank you so much. 

     

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

     

    Much appreciated