Forum Discussion
Filter table on dynamic, detached slicer values, then display filtered, aggregated data on a visual.
Hi anonymouseke,
you want both tables to show the same result even when Delivered on is not added, and it should work for both This week and Own period modes.this issue is happening because when Delivered on is not in the visual, Power BI groups the data by other columns, so the date filter doesn’t apply correctly.
The best way to fix this is to use a proper Date table and link it with your Delivered oncolumn. Then use that Date table in your slicers. This will keep your results the same whether Delivered
on is visible or not.
Hope this helps if you have any queries we are happy to assist you further.Regards,
Community Support Team.
Anonymous GeraldGEmerick , I have attached an example PBIX file!
Hello anonymouseke,
I checked your file and the reason it is not working is because the measure is using MAX(DeliveredOn) Since that column is not part of the visual, Power BI is grouping by StoreName and other fields, and only taking the latest date in that group. That is why the filter does not behave the way you expect.
Best option is to create a proper Date table and link it with your DeliveredOn column. Then use that Date table in the slicer. This will filter your data correctly even when DeliveredOn is not shown in the table. and
If you don’t want to create a relationship, then you need to change the measure so that it checks all DeliveredOn values in the current context, not just MAX. Then you can use that measure in the Filters pane and set it to 1.Using a Date table is the cleaner and more reliable option, because the filter will always apply correctly across visuals.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Support Team.Hi,
This works but I have an additional step in my report. Users can choose a "Filter mode", which dictates if it should filter on the date range provided OR the current week (eg. from Monday to today).
So somehow this slicer should be incorperated, how can I do that?
FilterMode = DATATABLE("Mode", STRING, {{"This week"}, {"Own period"}})You can find this example report here:
https://drive.google.com/file/d/18m5Em_c7x8RpuVfn9Q5zmBEOQ56bAUco/view?usp=sharing
PS: If you are wondering WHY I need this. I need the automatic emails to be sent with the 'Current week' so I always send the newest information!
Hope you can help me with this!
Kind regards
Hello anonymouseke,
I have reproduced your requirement and implemented the solution. I have attached the PBIX file for your review so you can see the updates. Let me know if you need any changes or further improvements.
Regards,
Comunity Support Team.