Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
UsePowerBI
Post Prodigy
Post Prodigy

How to replace values in joined columns from different tables?

Hello

 

I have used a common column to join three tables.

 

I dragged the common column to create a table in the dashboard. Then I need to do these:

 

1) I want to drag another column into the table that normally does not have blanks but due to being joined/lookedup by the common column, blanks are created. How do I replace these blanks with a specific value?

 

2) I want to drag another column and replace its values with "New" when there is 'YES' in either of two other columns. When this condition is not met, the values of the column should be left intact.

 

Any help please?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Use switch function.
Follow switch function dax for more number of column.

Thanks
Pravin

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@UsePowerBI The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

 

Example:

 

Source table 1:

1,a1

2,a2

3,a3

 

Source table 2:

1,b1

2,b2

4,b4

 

Source table 3:

1,c1

2,c2

3,c3

 

FIRST QUESTION:

When I join these two tables and I create a table component with the first column to be the first column of the first table and the second column to be the second column of the second table, it displays:

1,b1

2,b2

3,[blank]

 

I want to replace these blanks with "YES".

 

SECOND QUESTION:

When I create the table:

1,b1,c1

2,b2,c2

3[blank],c3

 

I want to replace the values in the third column with 'NO' when there is a blank in either first or second column. When this condition is not met, I would like to leave the values of the third column intact.

 

Thanks

 

 

Anonymous
Not applicable

For 1 st question.
Create column in first table
Col=If(isblank(related(table2[col2])),"yes")

2nd que
If(isblank(related(table2[col2])),"no",related(table3[col]))

Thanks
Pravin


Thanks for this, but can we do it if I need to make 20 replacements? I cannot do a nested IF formula with 20 entries.
Anonymous
Not applicable

Use switch function.
Follow switch function dax for more number of column.

Thanks
Pravin
Anonymous
Not applicable

Create column in 2nd table

 

NEw column=if(related(table3[Column]="Yes","New",table3[Column])

 

For first question,

 

I think we need to use lookupvalue or simply merge two queries.

Could you please share sample dataset and expected output.

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.