"Wildcard" Filtering

HI,

Please try the new input slicer - Create and use an input slicer - Power BI | Microsoft Learn

jabrillo​ 

could you pls provide more sample data for both two tables?

Hi jabrillo​,

Yes, this is possible. Since the relationship gives you an exact match, I would handle the wildcard part with a measure and use that measure as a visual-level filter on TV1.

Something like this:

Show User =
VAR SelectedDept =
SELECTEDVALUE ( tbl_instance[deptcode] )
VAR UserDept =
SELECTEDVALUE ( tbl_user[deptcode] )
VAR IsThreeDigitDept =
LEN ( SelectedDept ) = 3
&& NOT ISERROR ( VALUE ( SelectedDept ) )
RETURN
IF (
IsThreeDigitDept,
IF ( LEFT ( UserDept, 3 ) = SelectedDept, 1, 0 ),
IF ( UserDept = SelectedDept, 1, 0 )
)

Then add Show User to the Filters on this visual section for TV1 and filter it to 1.

This gives you:

003 → 003A, 003B, etc.
003A → 003A only
003B → 003B only
A123 → A123 only
B456 → B456 only

One thing to watch out for is your existing relationship between tbl_user[deptcode] and tbl_instance[deptcode_d]. Since that relationship is an exact match, it may filter out 003A/003B before the measure can apply the wildcard logic.

So if the measure doesn't return the expected result, I would look at the relationship/filter direction first. In that case, you may need to prevent that relationship from filtering TV1 and let the Show User measure handle the filtering instead.

The important part is that the wildcard logic should only be applied when the selected value is exactly three numeric characters. Otherwise, codes such as A123 should continue to use the normal exact-match behaviour.

Hi jabrillo​ 

The simplest solution is to use the filter pane. Outside this, only the input slicer supports wildcard filtering.

Yes, it is possible. The key is to use a DAX measure for the filtering logic, because the normal relationship only performs exact matching.

Solutions

Create a measure in tbl_user:

Show User =

VAR SelectedDept =

SELECTEDVALUE(tbl_instance[deptcode])

VAR UserDept =

SELECTEDVALUE(tbl_user[deptcode])

VAR First3Numeric =

NOT ISERROR(VALUE(LEFT(SelectedDept, 3)))

RETURN

IF(

First3Numeric,

IF(LEFT(UserDept, 3) = LEFT(SelectedDept, 3), 1, 0),

IF(UserDept = SelectedDept, 1, 0)

)

Then add Show User to TV1's visual-level filters and set it to 1.

Result

Selected in TV2 TV1 shows

A123 A123 only

B456 B456 only

003A 003A only

003B 003B only

003 003A + 003B

001 001A + 001B etc.

Important

Because you already have a relationship between the tables, the relationship may filter tbl_user before the measure runs. If that happens, the measure above alone won't solve 003 → 003A/003B.

In that case, the relationship needs to be changed to single-direction or made inactive, and the filtering should be handled by the measure.

Hi jabrillo​ ,

Thank you for confirming that the issue was resolved, could you please Mark the solution which helped in resolving the issue.

Thanks!!