Forum Discussion

Daniel48's avatar
Daniel48
Icon for Helper I rankHelper I
2 years ago
Solved

Union three columns across three tables

Hello,    Is it possible to create a column that only has distinct values from 3 columns on 3 different tables. I found the formula below but it keeps giving me errors.   Thank you in advance! ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    try this

     

    Master Policy Table =
    FILTER (
        DISTINCT (
            UNION (
                VALUES ( 'policy finance_Initial'[policy_uniqueid] ),
                VALUES ( 'policy finance_target'[policy_uniqueid] ),
                VALUES ( 'policy premium'[policy_uniqueid] )
            )
        ),
        [policy_uniqueid] <> BLANK ()
    )

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

Before you can do a UNION your columns need to have the same name.  Use SELECTCOLUMNS  to rename them as needed.

Daniel48's avatar
Daniel48
Icon for Helper I rankHelper I
2 years ago
Master Policy Table = FILTER(DISTINCT(UNION(VALUES('policy finance_Initial'[policy_uniqueid]),VALUES('policy finance_target'[policy_uniqueid]),VALUES('policy premium'[policy_uniqueid])),
    [policy_uniqueid] <> BLANK()))
 
I have renamed the columns but I am getting the error. Too many arguments were passed to the DISTINCT function. The maximum argument count for the function is 1. I can get it with two columns but not three
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    try this

     

    Master Policy Table =
    FILTER (
        DISTINCT (
            UNION (
                VALUES ( 'policy finance_Initial'[policy_uniqueid] ),
                VALUES ( 'policy finance_target'[policy_uniqueid] ),
                VALUES ( 'policy premium'[policy_uniqueid] )
            )
        ),
        [policy_uniqueid] <> BLANK ()
    )

    • Daniel48's avatar
      Daniel48
      Icon for Helper I rankHelper I
      2 years ago

      That worked perfectly! Thank you for all of your help!