Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated columns and slicer value

Hi, 

 

As a slicer value cannot be passed on to calculated column to make it dynamic, what would be the best work-around to below issue?

 

The column "Report_product" should be dependable on the date selected in the Date-slicer. In below example I have used today's date, but the date would need to be dynamic in order to be able to report historical numbers correctly.

 

 

 

Br, Chris

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Calculated column values are not responsive to filter selection or parameters. So if you want to return a dynamic value filtered by slicer , my suggestion is to create a measure instead of calculated column.

    (1)Create a calendar date table to return a date column .

    Calendar Date = CALENDAR(DATE(2021,12,28),DATE(2021,12,31))

    (2)Add a slicer with field 'Calendar Date'[Date] ,then you can filter data table with the dynamic date .

    (3)Create a measure with the dynamic date from Calendar Date table.

    Measure = IF(SELECTEDVALUE('Table'[Exp.date_q])=BLANK(),SELECTEDVALUE('Table'[Product_y]),
IF(and(SELECTEDVALUE('Calendar Date'[Date])>SELECTEDVALUE('Table'[Exp.date_y]),SELECTEDVALUE('Calendar Date'[Date])<SELECTEDVALUE('Table'[Exp.date_q])),SELECTEDVALUE('Table'[Product_y]),
IF(SELECTEDVALUE('Calendar Date'[Date])>SELECTEDVALUE('Table'[Exp.date_q]),SELECTEDVALUE('Table'[Product_m]))))

    The final result is as shown :

    I have attached my pbix file , you can refer to it.

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies