How to show records after wildcard

[some1else]

Hello guys, hope everyone is okay.

I've been trying to work a solution for a problem I'm facing but I'm out of ideas.

Consider the 2 tables below. I've setup some filters where the user starts filtering by the ID.

Each ID is composed of levels, separated by a dash.

User selects 1st ID level (1), then next filter shows the 2nd level (in this case only X1), then 3rd level (Y3).

I then need that all WO that start by those 3 levels to show up. Right now, if I click on the ID 1-X1-Y3-Z5-407 it only shows WO number 4, where it should also show the number 5, because that ID it's a child. PB should interpret something like 1-X1-Y3-Z5-407*.

How can I achieve this?

[smpa01 · Accepted solution]

@some1else  can be done with disconnected tables

[some1else]

Thank you both for the help. I've implemented the DAX solution just because I don't understand M or Power Query very well, but will try that approach also 🙂

As for the DAX solution, works if I select the ID on a table. If the user starts selecting (using slicers) the ID levels, is there a way of the selected value in filter measure to know that selection? I've tried using variables but failed to inject them into de selectedvalue function of that measure.

[v-rzhou-msft · Accepted solution]

Hi @some1else 

Do you want to catch select value in your slicer in your filter measure? I think selectvalue function can achieve your goal.

filter = IF(CONTAINSSTRING(MAX('Dax'[ID]),SELECTEDVALUE('Dax Slicer'[ID])),SELECTEDVALUE('Dax Slicer'[ID]),BLANK())

Here I will show you how to achieve your goal by Power Query.

Duplicate your ID column and then split your ID column by Delimiter (Custom -).

Then create a hierachy level in report view.

Best Regards,
Rico Zhou

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

[smpa01 · Accepted solution]

@some1else  can be done with disconnected tables

[speedramps]

Hi some1else

Please consider this solution and click the thumbs up because I took effort to help and click ACCEPT SOLUTION if it fixes your problem.

In Power Query use parse by delimiter to create id segments, eg

idseg1 = 1

idseg2 = X1

idseg2 =Y3

isseg3 = Z5

idseg4 = 407

idseg5 = 101

Group the segments into a hierarchy

Then add the hierarchy to the slicer

Voila, you can now select any branch !