How to add a column from table B to table A if a value exist or not

[RogerSteinberg]

Hi,

Assuming i have a date table:

Date

1-Jan

2-Jan

3-Jan

And a sales table:

Tdate	Sales	Product	PromoFlag
1-Jan	10	Soccer	N
2-Jan	20	Tennis	Y
2-Jan	20	Soccer	N
3-Jan	15	Basketball	N

I need to append a column to the Date table as such : 

Date	PromoPeriod
1-Jan	No
2-Jan	Yes
3-Jan	No

Basically the yes should be computed by looking in the sales table to see if there was during that day at least one product with the "Y" flag . If so then the date should be Yes 

How can i do this ? 

[PhilipTreacy · Accepted solution]

Hi @RogerSteinberg 

Download sample PBIX file with the code below

In your 'Date' table you can create a column using this code

Column = IF(CALCULATE(COUNTROWS('Sales'), FILTER('Sales', 'Sales'[Tdate] = [Date] && 'Sales'[PromoFlag] = "Y"))>0,"Yes","No")

However there are a few things to mention.

When one talks about a Date Table in Power BI this usually means a table that contains dates and other time related information that is used as a date table for Time Intelligence calculations.  Not sure that is what you have?

If it is, you shouldn't be adding columns to it to as described above.

Do you really need this PromoPeriod added as a column in your table?  Would a measure work just as well?

Regards

Phil

[RogerSteinberg · Accepted solution]

very well explained and thank you for your suggestions at the end! 

[PhilipTreacy · Accepted solution]

Hi @RogerSteinberg 

Download sample PBIX file with the code below

In your 'Date' table you can create a column using this code

Column = IF(CALCULATE(COUNTROWS('Sales'), FILTER('Sales', 'Sales'[Tdate] = [Date] && 'Sales'[PromoFlag] = "Y"))>0,"Yes","No")

However there are a few things to mention.

When one talks about a Date Table in Power BI this usually means a table that contains dates and other time related information that is used as a date table for Time Intelligence calculations.  Not sure that is what you have?

If it is, you shouldn't be adding columns to it to as described above.

Do you really need this PromoPeriod added as a column in your table?  Would a measure work just as well?

Regards

Phil