Forum Discussion

pbi1908's avatar
pbi1908
Helper III
3 years ago
Solved

Calculated Table with DAX

Hi guys, 

 

I have 2 tables, in one i have Projects and in the other i have tasks.

I would lilke to create a new table with 2 columns, in the first i want to have a union of distinct Names (Project Name and Taks Name), in the second i want an indication from which table the column is come from so the values will be like this (Project, Task). 

 

For the first Column i figured out how to create it (look the code below)

UNION(DISTINCT(Projects[ProjectName]), DISTINCT(Tasks[TaskName])).
 
For the second i don't know how to create it, can anyone help me ? 
 
I prefer a solution in DAX.
 
Thanks.
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your feedback.

    Please check the below DAX formula whether it suits your requirement.

    Additionally, if you want to change a column name, you can try to use SELECTCOLUMNS DAX function as well.

     

    New table =
SELECTCOLUMNS (
    UNION (
        ADDCOLUMNS ( DISTINCT ( Projects[Project name] ), "Table name", "Project" ),
        ADDCOLUMNS ( DISTINCT ( Tasks[Task name] ), "Table name", "Task" )
    ),
    "@Name", Projects[Project name],
    "@Table", [Table name]
)

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    New table =
UNION (
    ADDCOLUMNS ( Projects, "Table name", "Project" ),
    ADDCOLUMNS ( Tasks, "Table name", "Task" )
)
    • pbi1908's avatar
      pbi1908
      Helper III
      3 years ago

      Jihwan_Kim  Hmm yes since i have multiple columns in the table how can i select only the ProjectName from Projects table and only the TaskName from Tasks table instead of all the table?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Thank you for your feedback.

        Please check the below DAX formula whether it suits your requirement.

        Additionally, if you want to change a column name, you can try to use SELECTCOLUMNS DAX function as well.

         

        New table =
SELECTCOLUMNS (
    UNION (
        ADDCOLUMNS ( DISTINCT ( Projects[Project name] ), "Table name", "Project" ),
        ADDCOLUMNS ( DISTINCT ( Tasks[Task name] ), "Table name", "Task" )
    ),
    "@Name", Projects[Project name],
    "@Table", [Table name]
)