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.

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