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

[PowerAnalytics]

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

 

• 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

[jthomson]

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

[PowerAnalytics]

Hi Thomson! Can you elaborate your suggestion?

[PowerAnalytics]

bump

[v-huizhn-msft]

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

[PowerAnalytics]

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

[v-huizhn-msft · 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.



You can download the attachment for check more details.

Best Regards,
Angelia

[PowerAnalytics]

Amazing Angelia, you are the best!