If statement in data model with multiple tables

[fosterXO]

Hi,

There is a cube in my organization that is made of 4 different tables. They connect to each other based on various keys.

The different dim tables classify a sale in different ways.
I would like to create a new column in, lets say Table_A, that would create a new classifiction based on the values of the different tables. Such as:

IF Table_A - Column_1 = "Bike"  AND Table_B - Column_1 = "Blue" then "High_Priority"

IF Table_A - Column_1 = "Chair"  AND Table_B - Column_1 AND Table_C - Column_2= "Wood" then "Low_Priority"

How can this be done? Could you guide me in the right direction?

[123abc · Accepted solution]

In Power BI, you can achieve this by creating calculated columns or measures using DAX (Data Analysis Expressions). Here's a general approach to accomplish your task:

1. Identify the Keys: Ensure you have common keys across your tables to establish relationships.

2. Create Calculated Columns: You can create calculated columns in Table_A based on conditions from other tables. Here's how you could do it:

   • Go to the modeling view in Power BI.
   • Select the table (Table_A).
   • Click on "New Column" in the Modeling tab.
   • Write DAX expressions for your conditions.

3. Write DAX Expressions: Use the RELATED function to access columns from related tables. Here's a simplified example:

Priority =
IF(
Table_A[Column_1] = "Bike" && RELATED(Table_B[Column_1]) = "Blue",
"High_Priority",
IF(
Table_A[Column_1] = "Chair" && RELATED(Table_B[Column_1]) = "Wood" && RELATED(Table_C[Column_2]) = "Wood",
"Low_Priority",
"Other"
)
)

This DAX expression creates a new column called "Priority" in Table_A based on your conditions. You can adjust the conditions according to your specific requirements.

1. Repeat for Each Table Combination: You'll need to write similar DAX expressions for each combination of conditions involving different tables.

2. Ensure Relationships: Make sure that relationships between tables are properly established in the relationship view of Power BI. This is crucial for the RELATED function to work correctly.

3. Refresh Data: After creating the calculated columns, refresh your data to apply the changes.

Keep in mind that this approach assumes that you're creating a calculated column. If you want to calculate this dynamically, you might need to create measures instead of calculated columns. The process is similar, but you'll use the CALCULATE function to create measures.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.