Creating a calculated column from data in 2 different tables

[laurenh]

I want to create a calculated column that says if data exists in Table1[col1] return this data and if not (is null) return data in Table2[col2].  What is the DAX formula for this? The tables are related in a One (Table 1) to many (Table 2) relationship.

[laurenh]

Ultimately I am tring to build a stacked bar chart that shows how much we are spending in warranty.  For part of the year that data was stored in Table1(col1) below and for the other part of the year that data was in Table2(col2).  I want to create a final column which aggregates that data by showing the data from col 1 if text exists in that column or the data from col 2 if the text exists in that column.  Then the final column is used to build the stacked bar chart which will show how much money was spent per warranty type.

[Icey]

Hi @laurenh ,

For part of the year that data was stored in Table1(col1) below and for the other part of the year that data was in Table2(col2). 

---

First, it is suggested to combine the two tables into one if they have similar structures.

There are two primary ways of combining queries: merging and appending.

• When you have one or more columns that you’d like to add to another query, you merge the queries.
• When you have additional rows of data that you’d like to add to an existing query, you append the query.

For more details, please refer:

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Docs

Append vs. Merge in Power BI and Power Query - RADACAD

Then, if you don't want to combine them, it is suggested to create dimension tables to connect the two fact tables. For more details, please refer:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

And I create a sample for you to refer, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @laurenh ,

What do you mean "data exists in Table1[col1]"? Which column's data is used to campare with Table1[col1] and Table2[col2]? Could you share us a simple sample?

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

And here are some posts about matching, please check if they could meet your requirements:

Solved: Matching Data in two tables - Microsoft Power BI Community;

DAX - “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 - P3 Adaptive

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@laurenh 

could you pls provide some sample data and expected output?