Forum Discussion

ahmadhammoud's avatar
ahmadhammoud
Regular Visitor
2 years ago

Dynamic Filter

  1. Dears, 
  2. i have a table with multi Date for example:
  3. ID, Arrive Date, Charge Date, Dischare Date
  4. and i have a date slicer from 2020 to 2024 
    i need to filter the table have the column mentioned above by a drop down list 
  5. so if selected value from the list is Arrive then filter the table where Arrive Date between 
  6. ---> if slicer is filtered so filter the table between the slicer min and max dates 
  7. ---> if slicer is not filter so filter the table between last start and end month 
    the below DAX  not working with error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

    FIN_List_Posting_Date_Filter =
    VAR DateList = SELECTEDVALUE(My_Report_Date[Report],"Posting Date")
    VAR SlicerMIN = MIN(DateTimeTable[DateTime])
    VAR SlicerMAX = MAX(DateTimeTable[DateTime])
    /*VAR StartDateMIN= IF(NOT(ISFILTERED(My_Report_Date[Report])),[StartDate])
    VAR StartDateMAX= IF(NOT(ISFILTERED(My_Report_Date[Report])),[EndDate])*/
    VAR
    PostedFilter = SWITCH( TRUE(),
    DateList ="Posting Date" , IF(ISFILTERED(DateTimeTable[DateTime]),
    CALCULATETABLE(
    FILTER(Charges,
    Charges[POSTED_DT_TM] >= SlicerMIN &&
    Charges[POSTED_DT_TM] <= SlicerMAX + TIME(0, 59, 59))),
    CALCULATETABLE(
    FILTER(Charges,
    Charges[POSTED_DT_TM] >= [StartDate] &&
    Charges[POSTED_DT_TM] <= [EndDate]+1))))


    RETURN PostedFilter



1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Use a disconnected table for your slicer, and a measure filter for the visual.