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