98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Referencing two tables
Referencing two tables
Resolved · High Priority · Version 2016
Thu 27 Feb 2020, 14:55Delegate Luca said...
Luca has attended:
Power BI Reporting course
Referencing two tables
Hi
I have an actuals and a forecast column within a table that I need to merge.
I want to write an if formula that references a "Report Date" column in my Date Table.
I essentially want to create a new column with the following if statement:
IF(Date<='DateTable'[Repot Date], Actual, Forecast)
However, when attempting to reference the column in my date table, I am unable to do so.
Thu 27 Feb 2020, 16:15
Edited on Thu 27 Feb 2020, 16:16Trainer Jens said...
RE: Referencing two tables
Hi Luca,
Thank you for the forum question.
Is the date column in the table related to the 'DateTable'[Repot Date]?
Do you want to test if the date is less than current date?
The dates in the DateTable are only used for grouping and date intelligence. If you have the two columns related the value will be the same in both tables. If you have the date 1/1/2020 it will be related to 1/1/2020 in the DateTable. What you test in the if if the date is 1/1/2020:
IF(1/1/2020<=1/1/2020, Actual, Forecast)
or if the date is 5/5/2020
IF(5/5/2020<=5/5/2020, Actual, Forecast)
The tests will always return Actual.
If you have the relationships correct. You will have to use the Related function to reference a column in a related table.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 28 Feb 2020, 11:06Delegate Luca said...
RE: Referencing two tables
Hi Jens
Thanks for the reply.
I have created a column in my date table which contains the same period end date for every single row. This is essentially the monthly report date (which I will update every month).
So, what I want to do is test whether the dates associated with my marketing costs, are less than the "Report Date" in my Date Table.
Reason I need this if function is because I have an actuals column and a forecast columns, which both need to be merged into one to get a rolling actuals/forecast data point for the upcoming financial year.
This probably isn't the best way to do this, but couldn't think of an alternative. If you have an alternative solution then please advise.
Thanks,
Luca
Fri 28 Feb 2020, 19:05Trainer Jens said...
RE: Referencing two tables
Hi Luca,
The data model in power bi controls how you have to write the DAX formulas. I will need to know how your table is related to the DateTable. Do you have a active relationship from your DateTable to the table where you want to test if it is less than your report date. Is the foreign key the Date column in your table or do you have other dates in the table?
If your relationship is from your primary key in the DateTable to the date you want to test in your Fact table (transaction table), the if below should work:
=IF([Date I want to test in fact table]<=Related('DateTable'[Repot Date]), Actual, Forecast)
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 6 Mar 2020: Automatically marked as resolved.
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Master the Power BI Custom VisualsPower BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations. |
