D-1 functionality on PBIX with single date picker feature

[julsr]

Hi Everyone! I'm implementing what I commented in this thread, but now I have a problem. I'm using this approach, and it works when I'm not using the feature to have a single date picker as mentioned here. I've attached a Power BI file as an example (see Single Date page), and what I want to do is implement the D-1 approach, but I need to consider that:

1. I need the D-1 feature in a PBIX file that's connected to a semantic model, so how can I implement it with that?
2. The PBIX file is using the Single Date Picker in multiple visualizations (50 per page at least, 4 pages in the dashboard)

Here is the file https://drive.google.com/file/d/1NBmwpX_mrNqpCm_gKIG_5WS6LHy4S5O7/view?usp=sharing  and would be great to have an idea of how to implement a D-1 feature on it.

Hope you have an idea on this as you're the master on this D-1 feature v-cgao-msft

Thanks!

[Anonymous]

Hi @julsr ,

The Filter for the date column affect the Filter for the measure, and obviously, Power BI will prioritize the Filter for the column. Please don't set too many filters, the relationship between filters is "And" not "or".

Best regards,

Mengmeng Li

[Anonymous · Accepted solution]

Hi @julsr ,

As long as you don't bookmark a Preselected Slicer, its default selected date is automatically updated every day. If you need the data from other pages to be filtered synchronously, you just need to sync the slicer as I did before.

Also, the buttons in Power BI Desktop do not support filtering data directly. In addition to Preselected Slicer, you can also consider creating calculated tables and measures. But your report has many pages, so it's easier to create measures. Here is the method about measure for your reference:

check = VAR table1= ALL('dummy-sales-data'[Fecha])
VAR table2= ALLSELECTED('dummy-sales-data'[Fecha])
VAR test= IF (
    COUNTROWS ( EXCEPT ( table1, table2 ) ) = 0
    && COUNTROWS ( EXCEPT ( table2, table1 ) ) = 0,
    TRUE,
    FALSE
)
RETURN IF(test,IF(MAX('dummy-sales-data'[Fecha])=DATE(2024,1,19),1,2),IF(MAX('dummy-sales-data'[Fecha]) in table2,1,2))

When I click the "Clear all slicers" button, only set Date rows will be displayed in the table.

Other things you need to do:

1. Set filters for other visuals.

2. Manually adjust the column width or change the column background color to hide the "check".

———————————————————————————————————————————————————

Please choose the method that suits you according to your needs. If my answer solves this issue, please accept it as a solution, which will show it to more people who need help.

Best regards,

Mengmeng Li

[julsr]

Great explanation, thanks! So, just to confirm, it's not possible to have a button that when clicked filters the data to D-1 without being a bookmark, right? (because as I will be using it in the future, the D-1 will be different for all coming days).

The solution you propose is to add to the measures that I have now the D-1 condition, but this will affect when D-1 is not active, or I should have 2 measures for the same calculation, D-1, and others.

Do you think it could work to have the "Check" Measure you created as a slicer? So when the user selects 1 in the check slicer, all the data in the report will be filtered to meet this criteria?

[Anonymous]

Hi @julsr ,

Yes, as far as I know, the currently built-in buttons don't have the ability to filter data directly. 

The first method is the "D-1", where you don't need to set bookmarks, and the "Reset" and "Clear" buttons that come with the Preselected Slicer will basically meet your needs. The second method is to create a measure for the filter, but you'll need to add it to each visual you want to filter on and set the filter.

The above are two different methods, the "D-1" method is faster than creating a measure, but the Preselected Slicer does not support the selection of date ranges like the built-in slicer, and the second method allows you to use the built-in date slicer, but obviously it requires you to do a lot of repetitive work, such as setting filters and hiding the "check" column.

Best regards,

Mengmeng Li

[julsr]

Ok, understood. Thanks for your help! One last question I'm facing now: do you know how I can implement it with the Single Date functionality that I showed in the Power BI file?

It's a single date picker option that uses the "After" instead of the "In Between" option, and then to apply it to all the visualizations, I'm using the filter as shown in the image below. The problem is that when I use the "Clear All Slicers" option, it goes back to the first date of my dataset instead of considering the Check column you sent me. Here is the link to the updated PBIX file.

Thank you!

[Anonymous]

Hi @julsr ,

Are you confused about the "D-1" method provided and don't know how to apply it to your report? Since your data contains too few dates, I will provide you with each step based on the "D-1" method.

Before using "D-1" method, there is one point you need to note: the bookmark is taken as a screenshot, it will only show the default selection date when you added the bookmark. For example, the default date of today's preselected slicer is "12/24/2024", but when you open the table on "12/26/2024", the default date will not be automatically updated to "12/25/2024". This means that you need to recreate your bookmarks and publish your report.

Based on your description, if you also want to sync the default selected date (Today-1) to different pages, the easiest way to do this is to use the "Reset" option that comes with the preseleced slicer. Then sync it to other pages. Consider using the Preselected Slicer in combination with other slicers for better date filtering.

Step1. Get "Preselected Slicer" visual

Step2. Create Calculated Table.

_PreselectedSlicer = DATATABLE(
    "IsDirtySlicer1", BOOLEAN, 
    {
        {FALSE()},
        {TRUE()}
    }
)

Step3. Create Measure.

Measure = 
VAR __cur_date = SELECTEDVALUE('Date'[Date])
VAR __totay = TODAY()
VAR __result = __cur_date = __totay - 1
RETURN
    __result

Step4. Preselected Slicer.

Step5. Apply Preselected Slicer to other pages.

For an example of a Preselected Slicer, you can refer to this blog.

How to dynamically select the default values we ne... - Microsoft Fabric Community

Best regards,

Mengmeng Li

[julsr]

Oh, thanks! That could be the reason why it is not working. Do you know if it is possible to automatically set a button to always return or refresh the report to the last day from the actual day? (I.e. when today's date (actual date from the calendar no matter filters) is equal to 25/12/2024 the report must go back to 24/12/2024, when today is equal to 26/12/2024, it must go back to 25/12/2024 and so on...)

Here is a report with more data if needed.

Thanks!