Filter table on dynamic, detached slicer values, then display filtered, aggregated data on a visual.

[anonymouseke]

So I am trying to make a report for a customer.

The report has a couple of filters:

"Mode Selection": "This week" or "Own selection"

"Period Selection": When "Own selection" is selected, use this to filter dates

So now I have data comming into PowerBi, this data has a 'DeliveredOn' field per row, which should be used to filter the table. After that, it should display the result into a table. This table does NOT show the 'DeliveredOn' field and is grouped by the StoreName.

My measure looks like this:

ShowData DeliveryProductGroup = 
VAR SelectedMode = SELECTEDVALUE(FilterMode[Mode], "Deze week")
VAR SelectedDays = VALUES(FilterDagen[SortOrder])
VAR HasDaySelection = COUNTROWS(SelectedDays) > 0

VAR Today = TODAY()
VAR StartOfWeek = Today - WEEKDAY(Today, 2) + 1
VAR EndOfWeek = StartOfWeek + 6

VAR DeliveredDate = MAX('rep vwRepDeliveryProductGroup'[DeliveredOn])

VAR DateMatch =
    SWITCH(
        TRUE(),
        SelectedMode = "Deze week", DeliveredDate >= StartOfWeek && DeliveredDate <= EndOfWeek,
        SelectedMode = "Eigen periode", DeliveredDate IN VALUES('DateSlicer DeliveryProductGroup'[Date]),
        TRUE, TRUE
    )

RETURN
IF(DateMatch, 1, BLANK())

I then use this measure to filter my visual on 'ShowData DeliveryProductGroup is 1'. My issue is that this does not seem to work. When I do add the 'DeliveredOn' column to my visual, it does work. 

I suppose this is because the measure will be evaluated on each row of the visual and not the original table data.

I tried fixing this by making a new table like this below, this did not work since the slicer values are not being updated dynamically, only when refreshing the dataset. I have been going at this problem for over 8 hours. I could really use some help :D.

FilteredTable = 
SUMMARIZE(
    FILTER(
        'rep vwRepDeliveryProductGroup',
        {SAME FILTER AS ABOVE}
    ),
    'rep vwRepDeliveryProductGroup'[StoreName],
    'rep vwRepDeliveryProductGroup'[ParentProductGroupName],
    'rep vwRepDeliveryProductGroup'[GroupedProductGroupName],
    "RetourPercentage", ABS(
        DIVIDE(
            SUM('rep vwRepDeliveryProductGroup'[TotalSalesPriceRetours]),
            SUM('rep vwRepDeliveryProductGroup'[TotalSalesPriceLeveringen])
        ) * 100
    ),
    "AvgSalesPriceLeveringen", SUM('rep vwRepDeliveryProductGroup'[AvgSalesPriceLeveringen]),
    "AvgSalesPriceRetours", SUM('rep vwRepDeliveryProductGroup'[AvgSalesPriceRetours]),
    "AvgRetourPercentage", ABS(
        DIVIDE(
            AVERAGE('rep vwRepDeliveryProductGroup'[AvgSalesPriceRetours]),
            AVERAGE('rep vwRepDeliveryProductGroup'[AvgSalesPriceLeveringen])
        ) * 100
    ),
    "Visites", COUNT('rep vwRepDeliveryProductGroup'[DeliveryId]),
    "TotalSalesPriceLeveringen", SUM('rep vwRepDeliveryProductGroup'[TotalSalesPriceLeveringen]),
    "TotalSalesPriceRetours", SUM('rep vwRepDeliveryProductGroup'[TotalSalesPriceRetours])
)

So my question is essentially: How do I filter my data first based on dynamic, detached slicer values, then display this filtered data (aggregated) on a table visual.

Some other usefull information:

DateSlicer table:

DateSlicer DeliveryProductGroup = CALENDAR(MIN('rep vwRepDeliveryProductGroup'[DeliveredOn]), MAX('rep vwRepDeliveryProductGroup'[DeliveredOn]))

FilterMode table:

FilterMode = DATATABLE("Mode", STRING, {{"Deze week"}, {"Eigen periode"}})

Filter screenshot:

Here is an example PBIX:
https://drive.google.com/file/d/18m5Em_c7x8RpuVfn9Q5zmBEOQ56bAUco/view?usp=sharing

Above is my example and this image describes it:

I want the tables to have the same visual outcome. I do not want to display the 'VisitOn', but I want to use it for filtering. How do I achieve this dynamically without reloading my data.

If I can provide any more information, please let me know!

[anonymouseke]

@v-hjannapu @GeraldGEmerick , I have attached an example PBIX file!

[v-hjannapu]

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.

[anonymouseke]

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

[v-hjannapu]

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.

[anonymouseke]

Now we are back to the original question. When not including the DeliveredOn date, which I don't want to include, the table values are not correct. All of the above things should work.

So a 'This week' selector and a 'date range' where the user can choose. With the table with or without DeliveredOn values being the same.

[v-hjannapu]

Hi @anonymouseke,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[GeraldGEmerick]

@anonymouseke I believe that in many cases you can handle this by creating a measure that returns 0 or 1 based upon whether a row should be filtered or not. You can then add that measure to the Filters pane for the visual and then you can filter that measure to display either just 0 or just 1 depending upon your needs.

[anonymouseke]

Hi @GeraldGEmerick , I already do this, my slicer, `ShowData DeliveryProductGroup` behaves like you described. The issue is that this uses `MAX('rep vwRepDeliveryProductGroup'[DeliveredOn])`, and since the visual does not show the DeliveredOn, this gets grouped and always returns the latest value.

[GeraldGEmerick]

@anonymouseke I think that sample data would go a long way towards helping to figure out a solution to this.

[anonymouseke]

I will prepare a sample!

[v-hjannapu]

Hello @anonymouseke,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to
 @GeraldGEmerick   ,   for those inputs on this thread. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Regards,
Harshitha.