Filter that user can modify except default criteria that can't be removed

[Lierreyy]

Hi there,

I have a report where I have a default filter for a field and the user should be able to change the filter, but the default filter criteria can’t be removed. In essence the user should be able to add to the filter criteria, but not be able to remove the default filter criteria. Below are more details with some sample data that explains further.

I have two tables like the following:

Bill Cycle:

Bill ID	Window Start Date	Bill Cycle to Use	Bill Cycle Days Passed
1	5/9/2022	FALSE	11
2	5/10/2022	FALSE	10
3	5/11/2022	FALSE	9
4	5/12/2022	FALSE	8
5	5/13/2022	TRUE	7
6	5/16/2022	FALSE	4
7	5/17/2022	FALSE	3

There are a couple of calculated columns in this table as follows:

Bill Cycle Days Passed = TODAY() - 'Bill Cycle'[Window Start Date]
Bill Cycle to Use = IF(TODAY()-7 = 'Bill Cycle'[Window Start Date], TRUE(), FALSE())

Accounts:

Account ID	Bill ID	First Name	Last Name
1	1	Sample First Name 1	Sample Last Name 1
2	1	Sample First Name 2	Sample Last Name 2
3	1	Sample First Name 3	Sample Last Name 3
4	2	Sample First Name 4	Sample Last Name 4
5	2	Sample First Name 5	Sample Last Name 5
6	2	Sample First Name 6	Sample Last Name 6
7	3	Sample First Name 7	Sample Last Name 7
8	3	Sample First Name 8	Sample Last Name 8
9	3	Sample First Name 9	Sample Last Name 9
10	4	Sample First Name 10	Sample Last Name 10
11	4	Sample First Name 11	Sample Last Name 11
12	4	Sample First Name 12	Sample Last Name 12
13	5	Sample First Name 13	Sample Last Name 13
14	5	Sample First Name 14	Sample Last Name 14
15	5	Sample First Name 15	Sample Last Name 15
16	6	Sample First Name 16	Sample Last Name 16
17	6	Sample First Name 17	Sample Last Name 17
18	6	Sample First Name 18	Sample Last Name 18
19	7	Sample First Name 19	Sample Last Name 19
20	7	Sample First Name 20	Sample Last Name 20
21	7	Sample First Name 21	Sample Last Name 21

These two tables are related to eachother with the Bill ID.

I need to make a table visual that will show all the accounts for the 7th day (Bill Cycle Days Passed = 7). I have no issues doing this by either using a slicer or using the filter pane.

But my issue is that I also need the user to be able to select other days (e.g. if the user wants to select the 8th day, they should be able to). However, the user should never be able to remove the 7th day.

How do I make a filter that allows a user to modify, but one specific criteria (the 7th day always filtered) can’t be changed?

Thanks!

[DataInsights · Accepted solution]

@Lierreyy,

This solution uses a clone of the Accounts table with no relationships. Filtering is achieved via DAX instead of a relationship.

Create calculated table:

Accounts Visual = Accounts

Create measure:

Accounts to Display = 
VAR vFilter =
    FILTER (
        'Accounts Visual',
        'Accounts Visual'[Bill ID]
            IN VALUES ( 'Bill Cycle'[Bill ID] )
                || 'Accounts Visual'[Bill ID]
                    IN CALCULATETABLE (
                        VALUES ( 'Bill Cycle'[Bill ID] ),
                        'Bill Cycle'[Bill Cycle Days Passed] = 7
                    )
    )
VAR vResultWithSlicerSelection =
    CALCULATE ( COUNT ( 'Accounts Visual'[Account ID] ), vFilter )
VAR vResultWithNoSlicerSelection =
    CALCULATE (
        COUNT ( 'Accounts Visual'[Account ID] ),
        'Accounts Visual'[Bill ID]
            IN CALCULATETABLE (
                VALUES ( 'Bill Cycle'[Bill ID] ),
                'Bill Cycle'[Bill Cycle Days Passed] = 7
            )
    )
VAR vResult =
    IF (
        ISFILTERED ( 'Bill Cycle' ),
        vResultWithSlicerSelection,
        vResultWithNoSlicerSelection
    )
RETURN
    vResult

Create a table visual using fields from the Accounts Visual table, and create a visual filter using the measure above:

------------------------------

[Lierreyy]

Thank you @DataInsights & @v-easonf-msft!! These are great solutions and does exactly what I'm trying to do! Thank you both!

[v-easonf-msft · Accepted solution]

Hi， @Lierreyy 

Borrowing from @DataInsights ‘s solution, I made some changes.

Please try the following steps:
1. Add a seperated table as below:

Slicer = DISTINCT('Bill Cycle'[Bill Cycle Days Passed])

 2. Add a measure as below and apply it to the visual filter pane

visual filter = 
VAR _selected =
    IF (
        MAX ( 'Bill Cycle'[Bill Cycle Days Passed] )
            IN VALUES ( 'Slicer'[Bill Cycle Days Passed] )
                || MAX ( 'Bill Cycle'[Bill Cycle Days Passed] ) = 7,
        1,
        0
    )
VAR _unselected =
    IF ( MAX ( 'Bill Cycle'[Bill Cycle Days Passed] ) = 7, 1, 0 )
RETURN
    IF ( ISFILTERED ( 'Slicer'[Bill Cycle Days Passed] ), _selected, _unselected )

Best Regards,
Community Support Team _ Eason

[DataInsights · Accepted solution]

@Lierreyy,

This solution uses a clone of the Accounts table with no relationships. Filtering is achieved via DAX instead of a relationship.

Create calculated table:

Accounts Visual = Accounts

Create measure:

Accounts to Display = 
VAR vFilter =
    FILTER (
        'Accounts Visual',
        'Accounts Visual'[Bill ID]
            IN VALUES ( 'Bill Cycle'[Bill ID] )
                || 'Accounts Visual'[Bill ID]
                    IN CALCULATETABLE (
                        VALUES ( 'Bill Cycle'[Bill ID] ),
                        'Bill Cycle'[Bill Cycle Days Passed] = 7
                    )
    )
VAR vResultWithSlicerSelection =
    CALCULATE ( COUNT ( 'Accounts Visual'[Account ID] ), vFilter )
VAR vResultWithNoSlicerSelection =
    CALCULATE (
        COUNT ( 'Accounts Visual'[Account ID] ),
        'Accounts Visual'[Bill ID]
            IN CALCULATETABLE (
                VALUES ( 'Bill Cycle'[Bill ID] ),
                'Bill Cycle'[Bill Cycle Days Passed] = 7
            )
    )
VAR vResult =
    IF (
        ISFILTERED ( 'Bill Cycle' ),
        vResultWithSlicerSelection,
        vResultWithNoSlicerSelection
    )
RETURN
    vResult

Create a table visual using fields from the Accounts Visual table, and create a visual filter using the measure above:

------------------------------