Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ValeriaBreve
Post Patron
Post Patron

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
Post Patron
Post Patron

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors