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

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
4 years ago

Hello, I have prepared the .pbix with cleaned data - no confidential info and smaller ranges - and it worked! So going back to the previous file I realized that the measure was being filtered, the rows were still in the table only showing the volume as zero. As soon as I filtered for "volume not blank" it gave the expected results - so thank you so much for this! It is a completely new way of working for me giving me so much freedom on my reports. I have started watching the videos on tabular editor as well on the SQLBI website to get better at it :-).

 

The only downside of this method is that everything with the measure at zero is being filtered - in my case I have facilities that users would like me to show even if with no productions, that get removed from the visual once I apply the filter:

 

Before

After:

 

 

Does a workaround exist to still see those facilities?

 

Thanks!

Kind regards

Valeria

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