Forum Discussion
Use What-If parameter for selecting a date interval
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
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
great - that works. I will post again as soon as I can compile a pbix without confidential info. Thanks!
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
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.