Forum Discussion

aghanchi's avatar
aghanchi
Icon for Helper I rankHelper I
1 year ago
Solved

Filter Visuals on multiple date columns in a table using single date SELECTEDVALUE filter

I work for a nonprofit where we have to report case counts among other things. One of the reports asks for cases that are in open state at a certain date. The fact table has an OpenDate and CloseDate...
  • aghanchi's avatar
    aghanchi
    1 year ago

    I resolved it by adding the same Measure (shown below) that I used for the card visual into a table visual alongside with other fields that I needed for my case list report and it worked! I just now need to hide that measure column from the table because it is showing a "1" in every row 🙂

     

    Measure_OpenAsOfSelectedDate = 

Calculate(
    DISTINCTCOUNT(AllSRs[ServiceReqID]),
    AND( 
        AllSRs[OpenDate]  <= SELECTEDVALUE(Dates[Date], TODAY()) , 
        or( 
            ISBLANK(AllSRs[CloseDate]),     
            AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY())
        )
    )
)

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

aghanchi Try:

Measure_ISOpenAsOfSelectedDate = 
if(
    AND( 
        MAX( AllSRs[OpenDate] ) <= SELECTEDVALUE(Dates[Date], TODAY()) , 
        or( 
            ISBLANK(AllSRs[CloseDate]),     
            AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY())
        )
    )
    ,1
    ,0
)
  • aghanchi's avatar
    aghanchi
    Icon for Helper I rankHelper I
    1 year ago

    Greg, thank you for your response!

    Unfortunately, that didn't work. I had to apply the MAX function to other instances of OpenDate and CloseDate columns in the query to bypass the same error. In the end, when I applied tat measure as the visual filter, nothing happened. It did not return 1 or 0 either.

     

    Anyways, I was able to lookup the following measure that at least gets the totals right for the Card visual. I still want to resolve it for the table visual where I am listing the actual cases.

     

    Measure_OpenAsOfSelectedDate = 

Calculate(
    DISTINCTCOUNT(AllSRs[ServiceReqID]),
    AND( 
        AllSRs[OpenDate]  <= SELECTEDVALUE(Dates[Date], TODAY()) , 
        or( 
            ISBLANK(AllSRs[CloseDate]),     
            AllSRs[CloseDate] > SELECTEDVALUE(Dates[Date], TODAY())
        )
    )
)