Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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:
------------------------------
Proud to be a Super User!
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
Thank you @DataInsights & @v-easonf-msft!! These are great solutions and does exactly what I'm trying to do! Thank you both!
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
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:
------------------------------
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |