Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there
I have two tables for witch I have created a relationship that looks at crop.
One table holds a harvest date column and the other table holds number of days to grow plants.
I like to subtract the number of days from the harvest date to calculate the planting date.
Can anyone help me with this?
Solved! Go to Solution.
Hi Steve, thank you but this didnt work. I get an error stating that the column gow time doesnt exist or there is a relationship missing. I did create the relationship, but it is not beeing picket up in the function.
I have attached the pbix, I hope you don't mind thinking a look.
https://drive.google.com/file/d/1uZFHig9Jnv_6R1n1EONixlZ8UNBZbciF/view?usp=sharing
Hey @cocoloco79 . The relationship you had was many-to-many which is problematic. I updated your .pbix and it is linked here.
This was the relationship in your file:
I use this code to create a bridge table:
Grow Times = ALL( BI_Planting_Master[CropName], BI_Planting_Master[CropGrowTime])
And then updated the relationships:
And then updated the fields in the table visual:
Someone else may have a better way to do this.. but this is they way I know. Hope that works for you!
-Steve
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Steve, thank you but this didnt work. I get an error stating that the column gow time doesnt exist or there is a relationship missing. I did create the relationship, but it is not beeing picket up in the function.
I have attached the pbix, I hope you don't mind thinking a look.
https://drive.google.com/file/d/1uZFHig9Jnv_6R1n1EONixlZ8UNBZbciF/view?usp=sharing
Hey @cocoloco79 . The relationship you had was many-to-many which is problematic. I updated your .pbix and it is linked here.
This was the relationship in your file:
I use this code to create a bridge table:
Grow Times = ALL( BI_Planting_Master[CropName], BI_Planting_Master[CropGrowTime])
And then updated the relationships:
And then updated the fields in the table visual:
Someone else may have a better way to do this.. but this is they way I know. Hope that works for you!
-Steve
Hello,
One option is to create a calculated column in your Harvest Date table to pull in the grow time from the related table:
Grow Time = RELATED('Grow Times'[CropGrow Time] )
Then add a calculated column to subtract the days:
Planting Date = 'Harvest Date'[Date] - 'Harvest Date'[Grow Time]
Then create a table visual to match your example:
Here's a link to an example .pbix file.
-Steve