Slicer to search multiple column

[NickProp28]

Dear Community,

I have this sample of raw data and a measure, for example, if I click on 'A', the table will displays 'A' if a record is found in both the seller or buyer columns.

Visual Control = 
var tab = {max(Quote[Buyer]),MAX(Quote[Seller])}
var result = COUNTROWS(FILTER(DISTINCT('Name'[Buyer]),
COUNTROWS(FILTER( tab, 
[Value] = 'Name'[Buyer]) ) >0 )) +0
return
if ( result>0 , 1,0)

This measure works perfectly, but when I apply it to another PBIX which have million of raw values, the loading and result will take a very long time.

However, I am having trouble transforming this measure into a column to see if it speeds up the report loading. Or is there any solution to my issues ?

Any helps would be greatly appreciated!

Pbix: https://drive.google.com/file/d/1WhqMBEx71XlTS3Njzbpb0rODfufE7IZ5/view?usp=sharing

[DataInsights]

@NickProp28,

Try this measure:

Visual Control = 
VAR vNameSelection =
    VALUES ( 'Name'[Name] )
VAR vResult =
    IF (
        MAX ( Quote[Buyer] )
            IN vNameSelection
                || MAX ( Quote[Seller] ) IN vNameSelection,
        1
    )
RETURN
    vResult

The Name table contains all Buyer and Seller names, is the source of the Name slicer, and has no relationship with the Quote table. Use the measure [Visual Control] as a visual filter (equals 1).

---

[NickProp28]

Dear @DataInsights ,

Thanks for your attention.

Tried your measure in my PBIX (large dataset), the loading process took a long time. Is that possible to do it in calculated column?

[DataInsights · Accepted solution]

@NickProp28,

Try this solution.

1. Create calculated column in Quote table:

Buyer-Seller = Quote[Buyer] & "-" & Quote[Seller]

2. Create calculated table:

Name = 
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Quote,
            "Name", Quote[Buyer],
            "Buyer-Seller", Quote[Buyer-Seller]
        ),
        SELECTCOLUMNS (
            Quote,
            "Name", Quote[Seller],
            "Buyer-Seller", Quote[Buyer-Seller]
        )
    )
)

3. Create a many-to-many relationship (Name filters Quote):

Compare the performance and let me know. There's another approach using a bridge table and bidirectional filter if performance is still an issue.

https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/ 

[NickProp28]

Dear @DataInsights ,

Thank you! Calculated column takes less time than the measure, but since the relationship is many to many, I'm not sure if there are any issues will prompt out in the future.