In DAX formula, Conditionally Concatenate value from another table

[bbelman]

I am trying to use a DAX forumla to create a new table that requires me to reference existing data (which will be the rows in the table) and I also need to reference other existing data in order to conditionally concatenate a value to one of the columns. In SQL, I would do it like this.

CASE WHEN [SecondaryTable].[Rate] != '1.0'
THEN CONCAT ([PrimaryTable].[Summary], ' - ', [SecondaryTable].[Description])
ELSE [PrimaryTable].[Summary]
END
AS [Summary]

I have tried to recreate the table using SELECTCOLUMNS but I cannot reference the secondary table inside the function as it is out of scope. Is there a way to accomplish this in DAX? Both tables habe been imported into Power BI Desktop

[v-jingzhang · Accepted solution]

@bbelman You can create a new table with the following measure:

Table 2 =
VAR __table =
    ADDCOLUMNS (
        'Primary Table',
        "combine",
            IF (
                RELATED ( 'Secondary Table'[rate] ) <> 1,
                'Primary Table'[summary] & " - " & RELATED ( 'Secondary Table'[descripton] ),
                'Primary Table'[summary]
            )
    )
RETURN
    SELECTCOLUMNS ( __table, "id", [id], "summary", [combine] )

[bbelman]

Worked perfectly. Thank you! I expect the other posted solution provided will also work but performing the action in a measure as opposed to a calcualted column provides better proformance by keeping the dataset lean.

[lbendlin]

show sample data and expected outcome. This should be doable.

[bbelman]

Primary Table

id	summary	billed_at
1	Reformat Hard Drive	1
2	Add new users to directory	1
3	Add new users to directory	2

Secondary Table

id	descripton	rate
1	regular	1.0
2	overtime	1.5

Output Table

id	summary
1	Reformat Hard Drive
2	Add new users to directory
3	Add new users to directory - overtime

Note: The "billed_at" column in the primary table here is achieved in my use case through JOINs in SQL and RELATIONSHIPS in Power BI and is not directly included in the table. 

[lbendlin]

Join the tables via [Billed at]<-[id]

then have a calculated column in the 'Primary Table'

Summary Combined = [summary] & if(related('Secondary Table'[description])<>"regular"," - " & related('Secondary Table'[description]),"")