Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
4 years ago
Solved

dynamic slicer

Hi there

I have DAX code and my question how to make the Customer[Birthday] -- to make it dynamic as a slicer

TotalCustomers =
VAR myTable =
CALCULATETABLE(
SUMMARIZE(Customer,Customer[CustomerKey],Customer[Birthday]),
FILTER(
DISTINCT( Customer[CustomerKey] ),
CALCULATE( MAX( Customer[Age] ) ) > 30
)
,Customer[Birthday] >= DATE( 1980, 1, 1 ) && Customer[Birthday] <= DATE( 2022, 12, 31 )
)

Return
COUNTROWS(myTable)
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Oded-Dror ,

     

    Create a calendar table like:

    calendar = calendarauto()

    Then create a date-between slicer use the date column in calendar table.

    At last, modify the formula as below:

    TotalCustomers =
    VAR myTable =
    CALCULATETABLE(
    SUMMARIZE(Customer,Customer[CustomerKey],Customer[Birthday]),
    FILTER(
    DISTINCT( Customer[CustomerKey] ),
    CALCULATE( MAX( Customer[Age] ) ) > 30
    )
    ,Customer[Birthday] >= MIN('calendar'[date]) && Customer[Birthday] <= MAX('calendar'[date])
    )
    Return
    COUNTROWS(myTable)
    If I misunderstood your meaning, please show some sample data and expected result.
     
    Best Regards,
    Jay

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Oded-Dror ,

     

    Create a calendar table like:

    calendar = calendarauto()

    Then create a date-between slicer use the date column in calendar table.

    At last, modify the formula as below:

    TotalCustomers =
    VAR myTable =
    CALCULATETABLE(
    SUMMARIZE(Customer,Customer[CustomerKey],Customer[Birthday]),
    FILTER(
    DISTINCT( Customer[CustomerKey] ),
    CALCULATE( MAX( Customer[Age] ) ) > 30
    )
    ,Customer[Birthday] >= MIN('calendar'[date]) && Customer[Birthday] <= MAX('calendar'[date])
    )
    Return
    COUNTROWS(myTable)
    If I misunderstood your meaning, please show some sample data and expected result.
     
    Best Regards,
    Jay
    • Oded-Dror's avatar
      Oded-Dror
      Helper III
      4 years ago

      With this approch it show nothing so I changed it to 
      Customer[Birthday] >= MIN('Customer'[Birthdate]) && Customer[Birthday] <= MAX('Customer'[Birthdate])
      And it works as a measure