Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DokterT
New Member

partial string filter based multiple selectedvalues

Hi all,

 

I've been looking all over the web to achieve something (that might not even work in PowerBI).
Anyhow, hopefully one of you is able to help me out here 🙂

This is my problem;

I have a fact table with a Group-Name column, that sometimes contain multiple Group-names.

In order to filter this table i have created an unrelated filter table with all the relevant group names; (which is used as a slicer-list visual).

 

_Type_Filter =
DATATABLE (
"Subgroup", STRING,{{"Group01", etc}})

 

On the basis of de slicer-selection the table is filtered if the value of underlying formula is unequal to 1.

 

FilterText =
VAR searchvalue =
SEARCH (
SELECTEDVALUE( _Typen_Filter[Subgroup] ),
SELECTEDVALUE(Facttable[GroupColomn]),,
BLANK())
return
IF(searchvalue>0,1,0)

 

This works as long as there is only 1 selectedvalue in de subgroup filter selection. 

I just cant seem to get 2 or more values working. 


I have tried to change it to allselected, but this doesnt seem to do the trick. 

 

What am I doing wrong, and is it even possible in PowerBI?

 

Thanks in advance !!

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@DokterT 

I'm not sure exactly how you want to use this so I've created one of the alternatives. With it you can see the code pattern and adapt it as necessary.

1. Place FactT[Group] in a table visual

2.  Place SlicerT[Group] on a slicer

3. Create this measure. :

 

Show measure =
COUNTROWS (
    FILTER (
        DISTINCT ( SlicerT[Group] ),
        SEARCH ( SlicerT[Group], SELECTEDVALUE ( FactT[Group] ), 1, 0 ) > 0
    )
) + 0

 

4. Set the measure above as a filter for the table visual. Choose Show then value is greater than 0. This will filter out the unwanted rows in tehe visual.

See it all at work in the attached file with some simplified tables

image.png

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
DokterT
New Member

Thank a lot @AlB !! This did the trick 🙂

AlB
Super User
Super User

@DokterT 

I'm not sure exactly how you want to use this so I've created one of the alternatives. With it you can see the code pattern and adapt it as necessary.

1. Place FactT[Group] in a table visual

2.  Place SlicerT[Group] on a slicer

3. Create this measure. :

 

Show measure =
COUNTROWS (
    FILTER (
        DISTINCT ( SlicerT[Group] ),
        SEARCH ( SlicerT[Group], SELECTEDVALUE ( FactT[Group] ), 1, 0 ) > 0
    )
) + 0

 

4. Set the measure above as a filter for the table visual. Choose Show then value is greater than 0. This will filter out the unwanted rows in tehe visual.

See it all at work in the attached file with some simplified tables

image.png

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

DokterT
New Member

@AlBThanks for the advice, will try and get that to work.

As for a sample with fictional names (fruits/veggies might not be the best reallife example but you get the point hopefully);

 

The unrelated filter table contains one column with the follwing groups;

- "Apples"
- "Bananas"

- "Melons"

- "Onions"

- "Potatoes"

 

Sample of facts table;

ActivityID | Group                      | Details 

123212         | Potatoes // Onions  | jalfjklajf
142232         | Potatoes                  | kjfkljakdfa
154767         | Melons                    | jkdhflahfhajkf
234234         | Bananas // Melons  | dadfjhejks

So what I basically want to achieve is to filter the fact table based on the selection in the unrelated filter table.

Hence, selecting potatoes will give me;

ActivityID | Group                      | Details 

123212         | Potatoes // Onions  | jalfjklajf
142232         | Potatoes                  | kjfkljakdfa

Selecting Melons AND Onions should give me;

ActivityID | Group                           | Details 

123212         | Potatoes // Onions  | jalfjklajf
154767         | Melons                    | jkdhflahfhajkf
234234         | Bananas // Melons  | dadfjhejks

 

And all other variations based on multiple selections.

AlB
Super User
Super User

@DokterT 

You can probably use CONCATENATEX( ) to get all the entries from the slicer in one string.  For a more accurate answer I would need you to provide an example based on sample data of how things would work. I get the broad idea but need more details.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors