Forum Discussion

xcrmadmin's avatar
xcrmadmin
Helper I
5 years ago

Filter Subset table DATA based on selected slicer date

Hello experts, 

I have a subset data table named LOSTLIST already filterd and this table doesn't have any relationship with the calendar table and we need that. we need to select a date from the slicer to filter the DATA in LOSTLIST visual. 

I create the LOSTLIST table using below DAX code:

Lost List =
var xy =
FILTER(
Opportunity,
Opportunity[actualclosedate] <= [actualslectd] &&
YEAR(Opportunity[actualclosedate]) = YEAR([actualslectd]) &&
Opportunity[actualclosedate] <> BLANK() &&
Opportunity[statecode] = 2
)
Return xy

Hint: the [actualslectd] is a measure that hold only one selected date value from the silcer.

then we add the LOSTLIST columns in visual but it's not filtered by slicer and this is because there is no PHYSICAL relation between calendar and LOSTLIST table.

so how do we filter LOSTLIST table from the date slicer selected and show the DATA result in that visual? considering that we cant use  PHYSICAL relation.

please I need help

 

Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    xcrmadmin , You can use a measure like

     


    measure =
    VAR _max = MAXX(allselected('Date'),'Date' [Date])
    var _min = MinX(allselected('Date'),'Date' [Date])
    return
    calculate(countrows(Opportunity), Opportunity, Opportunity[actualclosedate] >=_min && Opportunity[actualclosedate] <=_max && Opportunity[statecode] = 2)

     

     

    • xcrmadmin's avatar
      xcrmadmin
      Helper I
      5 years ago

      Thank you for your reply, Actually, I need to display rows of data filtered based on the selected slicer date. 😞

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi xcrmadmin ,

     

    According to your description, whether you can provide test data (delete sensitive information) and the expected results. I will answer for you as soon as possible.


    Best Regards,
    Henry