Forum Discussion

bbelman's avatar
bbelman
New Member
5 years ago
Solved

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 c...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    @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] )

    v-jingzhang_0-1602036197985.png

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

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

bbelman's avatar
bbelman
New Member
5 years ago

Primary Table

idsummarybilled_at
1Reformat Hard Drive1
2Add new users to directory1
3Add new users to directory2

 

Secondary Table

iddescriptonrate
1regular1.0
2overtime1.5

 

Output Table

idsummary
1Reformat Hard Drive
2Add new users to directory
3Add 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's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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]),"")