Forum Discussion
Creating a calculated column from data in 2 different tables
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.
4 Replies
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.
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.
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
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.
