Forum Discussion

lundande's avatar
lundande
Frequent Visitor
4 years ago
Solved

Filter dataset for SELECTEDVALUE() using SEARCH()

I have a data set of 15 million rows that needs to be filtered based on a value that can appear in a any one of 6 seperate columns ( not a great dataset). I concatenated these columns so I can use SE...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    lundande  does this work?

    Measure =
VAR company =
    SELECTEDVALUE ( 'All Columns'[COMPANYNAME] )
RETURN
    CALCULATE (
        MAX ( 'All Columns'[COMPANYNAME] ),
        SEARCH ( company, 'All Columns'[Search Column], 1, -1 ) > 0
    )
dvl_ctaul's avatar
dvl_ctaul
Icon for Helper I rankHelper I
4 years ago

Are you ever going to want the rows that don't have the specified value? If so I would recommend filtering those out in your query editor before even loading to your dataset. Otherwise all of your calculations that will rely on that filter will need to contain that filter statement. 

You can just use that concatenated column you created and then do a filter by contains in powerquery.