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())
        )
    )
)

     

aghanchi's avatar
aghanchi
Icon for Helper I rankHelper I
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())
        )
    )
)