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.

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.