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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
PowerAnalytics
Advocate II
Advocate II

Combined Slicer Values -> Merged into one string -> Used as Table Filter

Hello! I have one very interesting challenge. If anyone has a solution for this, would be really amazing!

 

I would like to do the following:

 
  • Have 3 slicers that users can filter
  • Combine the text of the 3 into one single string
  • Use this combined string to filter one table (by 1st column being equal to the referred string)
  • All the info on the other rows will not be displayed
 Challenge.JPG
 
  • If more than one value from each slicer is selected, do not consider it
  • The lists that support the 3 slicers can be into 3 single column tables or 1 table with 3 columns if each selection does not affect the context of the other column values

 

 
1 ACCEPTED SOLUTION

Hi @PowerAnalytics

At first, please create two measures to get the selected values from two slicers. 

selectedCode1 = SELECTEDVALUE(Code1[Code1])
selectedCode2 = SELECTEDVALUE(Code2[Code2])


Then create a measure to get merged code using the formula below.

Filtered_mergedCode =
IF (
    FIRSTNONBLANK ( Data[Merged_Code], Data[Merged_Code] )
        = Code1[selectedCode1] & Code1[selectedCode2],
    FIRSTNONBLANK ( Data[Merged_Code], Data[Merged_Code] ),
    BLANK ()
)


You will get expected result as follows.

1.PNG2.PNG

You can download the attachment for check more details.

Best Regards,
Angelia

View solution in original post

7 REPLIES 7
jthomson
Solution Sage
Solution Sage

I'd try to do this backwards - have your data pull out the separate AB and 10 sections and then feed those columns into filters

Hi Thomson! Can you elaborate your suggestion?

bump

Hi @PowerAnalytics,

Based on my understanding, the col1, col2 and merged column from different tables. I assume col1 and col2 come from Table2, the Merged, country etc come form Table1. Please create a measure using the following fomula. 

Merged =
CALCULATE (
    FIRSTNONBLANK ( Table1[Merged], Table1[Merged] ),
    FILTER (
        Table1,
        IF (
            COUNTROWS ( ALLSELECTED ( Table2[col1] ) ) = 1
                && COUNTROWS ( ALLSELECTED ( Table2[col2] ) ),
            CONCATENATE ( SELECTEDVALUE ( Table2[col1] ), SELECTEDVALUE ( Table2[col2] ) )
                = Table1[Merged],
            TRUE ()
        )
    )
)


Then add the measure in the table you want to display. And please share your .pbix file if you still have other issue.

Best Regards,
Angelia

Hello huizhn!

 

Thank you so much for your reply!

 

I tried to apply your formula but had no success.

 

Let me attach the PBIx as you suggested, and you are able to show me how this can work it would be amazing!

 

PBIX here

Hi @PowerAnalytics

At first, please create two measures to get the selected values from two slicers. 

selectedCode1 = SELECTEDVALUE(Code1[Code1])
selectedCode2 = SELECTEDVALUE(Code2[Code2])


Then create a measure to get merged code using the formula below.

Filtered_mergedCode =
IF (
    FIRSTNONBLANK ( Data[Merged_Code], Data[Merged_Code] )
        = Code1[selectedCode1] & Code1[selectedCode2],
    FIRSTNONBLANK ( Data[Merged_Code], Data[Merged_Code] ),
    BLANK ()
)


You will get expected result as follows.

1.PNG2.PNG

You can download the attachment for check more details.

Best Regards,
Angelia

Amazing Angelia, you are the best!

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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