Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
4 years ago
Solved

Use What-If parameter for selecting a date interval

Hi, I wanted to use the what-if parameters for users to be able to select: 1) Number of days before production date 2) Number of Days after production date   So as to give them a relative date i...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    A calculated column won't work as it is only calculated during data refresh, it does not take into account any filters or slicers so won't update when your user changes the what if parameter.

    You could create a calculation group with a single calculation item, something like

    Filtered days calculation =
VAR numDays = SELECTEDVALUE ( 'What if parameter'[Num days] )
VAR baseDate = TODAY ()
VAR startDate = baseDate - numDays
VAR endDate = baseDate + numDays
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Date'[Date], startDate, endDate )
    )

    then apply that as a filter to the visuals which you want to be filtered

johnt75's avatar
johnt75
Super User
4 years ago

In principle you could return 0 instead of blank for the facilities you want to see, but I'm not sure how well 0 would appear in your visuals.

To achieve it, you could create a disconnected table with the facilities you always want to show, then in either your measures or calculation items you could do something like

var result = existing calculation
return IF ( SELECTEDVALUE( 'Table'[Facility] ) IN VALUES( 'List of facilities needed'[Facility] ),
COALESCE( result, 0), result )

Keep reading the SQLBI articles, check out their youtube channel and, if you can, take their Mastering DAX course. It is excellent.

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
4 years ago

I did buy the Mastering DAX course from SQLBI 🙂 It is a matter now to consistently find some time every day to go through it.... as always learning is so important as it allows you to do things more quickly and efficiently but finding the time to learn can be a struggle!

 

Anyway... thanks a lot for the advice on zero/blank! I will definitely try it 🙂

Thanks again for all your help!!!

Kind regards

Valeria