I have a table that I want to filter by more than one startsWith in the Visual

[jburbano]

I have a table with an definitionName columns that has some prefixes:

APIC-

ADF-

FA-

The built in filtering only allows for two and I need this to grow as it scales.  

I already have a list coming from the model, with the list of prefixes that I need to filter the table by at that specific column based on their individual values.  Not sure how to go on further from here.

I know I need a measure, but not sure how.

[dharmendars007 · Accepted solution]

Hello @jburbano , 

You can create a DAX measure to check if any of the prefixes from the PrefixTable exist at the beginning of the values in the definitionName column.

IsFilteredRow =
IF (ISBLANK (
SELECTCOLUMNS (
FILTER (
PrefixTable,
LEFT ( 'YourTable'[definitionName], LEN ( PrefixTable[Prefix] ) ) = PrefixTable[Prefix]),
"Filtered", PrefixTable[Prefix])),
0, -- Not a filtered row
1 -- Filtered row)

Add this measure to a table visual or use it as a slicer filter to only show rows with matching prefixes

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[FarhanJeelani · Accepted solution]

Hi @jburbano,

You want to filter a table where the definitionName column starts with any of the prefixes in your list (like APIC-, ADF-, or FA-). Since the built-in filters only allow two conditions, we can handle this with a DAX measure. Here’s how:

1. Get Your Prefixes
   Make sure you have a table in your model (let’s call it PrefixTable) with a column that lists all your prefixes. If you don’t have one, create it.

2. Write a Measure
   Create a measure in your model to check if a row in definitionName starts with any prefix. Here’s the formula:

   FilterByPrefixes =
   IF (
       SUMX (
           PrefixTable,
           IF (
               LEFT(YourTable[definitionName], LEN(PrefixTable[Prefix])) = PrefixTable[Prefix],
               1,
               0
           )
       ) > 0,
       1,
       0
   )

   This measure loops through all the prefixes in PrefixTable and checks if the start of definitionName matches any of them. If it does, it returns 1; otherwise, 0.

3. Add It to Your Visual Filter

   • Drag the measure (FilterByPrefixes) into the Filters on this visual area.
   • Set it to filter where the value is 1. Now your table will only show rows where definitionName starts with one of your prefixes.

4. Why This Works
   It’s flexible because as you add or remove prefixes in the PrefixTable, your filter updates automatically.

Please mark this as solution if it helps you. Appreciate Kudos.

[v-yangliu-msft · Accepted solution]

Thanks for the reply from FarhanJeelani  and dharmendars007 , please allow me to provide another insight: 

Hi  @jburbano ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Slicer_Table'[Select])
RETURN
IF(
FIND(
    _select,MAX('Table'[Group]),1,BLANK())=1,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FarhanJeelani · Accepted solution]

Hi @jburbano,

You want to filter a table where the definitionName column starts with any of the prefixes in your list (like APIC-, ADF-, or FA-). Since the built-in filters only allow two conditions, we can handle this with a DAX measure. Here’s how:

1. Get Your Prefixes
   Make sure you have a table in your model (let’s call it PrefixTable) with a column that lists all your prefixes. If you don’t have one, create it.

2. Write a Measure
   Create a measure in your model to check if a row in definitionName starts with any prefix. Here’s the formula:

   FilterByPrefixes =
   IF (
       SUMX (
           PrefixTable,
           IF (
               LEFT(YourTable[definitionName], LEN(PrefixTable[Prefix])) = PrefixTable[Prefix],
               1,
               0
           )
       ) > 0,
       1,
       0
   )

   This measure loops through all the prefixes in PrefixTable and checks if the start of definitionName matches any of them. If it does, it returns 1; otherwise, 0.

3. Add It to Your Visual Filter

   • Drag the measure (FilterByPrefixes) into the Filters on this visual area.
   • Set it to filter where the value is 1. Now your table will only show rows where definitionName starts with one of your prefixes.

4. Why This Works
   It’s flexible because as you add or remove prefixes in the PrefixTable, your filter updates automatically.

Please mark this as solution if it helps you. Appreciate Kudos.

[dharmendars007 · Accepted solution]

Hello @jburbano , 

You can create a DAX measure to check if any of the prefixes from the PrefixTable exist at the beginning of the values in the definitionName column.

IsFilteredRow =
IF (ISBLANK (
SELECTCOLUMNS (
FILTER (
PrefixTable,
LEFT ( 'YourTable'[definitionName], LEN ( PrefixTable[Prefix] ) ) = PrefixTable[Prefix]),
"Filtered", PrefixTable[Prefix])),
0, -- Not a filtered row
1 -- Filtered row)

Add this measure to a table visual or use it as a slicer filter to only show rows with matching prefixes

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN