cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValeriaBreve
Helper IV
Helper IV

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 in the report that contains both past and future days - existing relative date slicer is either one or the other.

 

So I added the 2 parameters, and used them with a calculated column to check if every order production date would fall into the interval.

 

It works for the first parameter setting however it does not refresh when I change the parameters further. It's the first time I use what-if parameters so maybe this is normal? If it is, what am I supposed to do to have a configurable relative date?

 

Thanks!

Kind regards

Valeria

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

11 REPLIES 11
ValeriaBreve
Helper IV
Helper IV

Hello, thanks, I have tried this soltuion, however I cannot make it work. The expression is giving me a red rectangle in the editor at the end, but without telling me what's wrong - I did check with cards that the startDate/endDate were calculating correctly and this is the case, so the issue must be with the final formula  

CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Date'[Date], startDate, endDate )
    )

 I tried to put this a a filter in the visual and applying to when it is not blank - but result is a blank visual.

Sorry but I don't know much about SELECTEDMEASURE() and how to debug this...

Thanks!

Have you just put that into a measure in Power BI desktop? You need to create a calculation group and calculation item in Tabular Editor.

Hello, sorry! I don't have the habit to do this, I really need to learn. So I did add the calculation group/item in tabular editor and then added this as a filter to my visual, but it is not filtering anything when selecting the calculation item...

Can you understand from my screenshots what I am not doing correctly?

Thanks!

Kind regards

Valeria

 

ValeriaBreve_0-1661958394206.png

 

ValeriaBreve_1-1661958676950.png

 

 

Do you have a proper measure on your visual, rather than just dragging a column on to it and letting Power BI do an automatic aggregation ?

Is it possible to share your PBIX file with any confidential information removed ?

Hello, yes I do have a proper measure - a sum of an amount. My PBIX file has a ton of confidential info but I will try to build a similar one (as soon as I have some time available) and send it to you - should I upload it here or send via e-mail? Thanks!

You'd need to use a service like We Transfer, or share a link directly from Google Drive or OneDrive. You can either post the link here or send me a private message

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

ValeriaBreve_0-1662036485508.png

After:

 

ValeriaBreve_1-1662036545727.png

 

Does a workaround exist to still see those facilities?

 

Thanks!

Kind regards

Valeria

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.

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

 

great - that works. I will post again as soon as I can compile a pbix without confidential info. Thanks!

johnt75
Super User
Super User

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.