Forum Discussion
In DAX formula, Conditionally Concatenate value from another table
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
@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] )
5 Replies
show sample data and expected outcome. This should be doable.
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.
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]),"")
@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] )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.
