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

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.