POWER BI - DAX - Measure filter

Question

I have a dax measure . This measure have 1 data . This is "GOOGLE";"YOUTUBE";"AMAZON"

I want to use this 1 line string result in FILTER.

CALCULATE(SUM(_TABLE);_TABLE.COMPANIESNAME; FILTER(_TABLE.COMPANIESNAME IN { mymeasure } ))

Does anyone can help me solve this problem ?

Thank you for help

Answer 1

There are probably way better ways to do what you want. You are treating Power BI like a relational database when you should be using it like a Star Schema. But without more info, I'm just going to answer the question.

Here's my sample table:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNrMrPU9JRMlSK1YlWcs/PT89JBXKNwNzI/NKQ0iQQ3xjMd0tMTk3Kz88GCpgoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}})
in
    #"Changed Type"

I don't have your DAX measure or its name, so I'm using this:

CompanyList = """Google"";""YouTube"";""Amazon"""

Just to prove it's the same as your measure, here it is in the report:

From this post I created a DAX formula that will parse your DAX value into a table with one row for each company name. Add this as a DAX table from Modeling > New Table. I named mine "Filtered table".

Filtered table = VAR CommaSeparatedList = [CompanyList]
VAR BarSeparatedList =
    SUBSTITUTE ( CommaSeparatedList, ";", "|" )
VAR Length =
    PATHLENGTH ( BarSeparatedList )
VAR Result =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, Length ),
        "Company", SUBSTITUTE( PATHITEM ( BarSeparatedList, [Value] ), """", "")
    )
RETURN
    Result

Here's what the table looks like:

Add a relationship between the two tables like this (Modeling > Manage relationships > New...):

Then add a DAX column to the filtered table by selecting the table and then Modeling > New Column

Count = CALCULATE(SUM('Table'[Count]))

You can total it up with this DAX measure:

Filtered total = SUM('Filtered table'[Count])

Change the CompanyList measure, and result will update: