Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to Solution.
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
Proud to be a Super User!
very well explained and thank you for your suggestions at the end!
very well explained and thank you for your suggestions at the end!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |