topic Date table with multi relationships in DAX Commands and Tips

Date table with multi relationships

gwright15 — Thu, 23 May 2019 21:22:55 GMT

I am hoping for some help with date tables - i'm new to PowerBI

I have a table, with 4 main date fields - add_date, Won_date, lost_date and latest_date.

I would like to report revenue based on the first three date. I have been using the USERELATIONSHIP function to switch relationships and this seems to be working.

However, the revenue value is the base currency rather than GBP, which is causing me the issue.

I have an exchange rate table, which is looking up the currency and latest_date for the correct ex-rate rate.

However, as they are using the same date table, it's not working, as we can only change the active relationship one it seems. I have got around this introducing a second date table, linking the Latest_date to this date table. However, this doesn't feel like best practice having multi date tables.

Please help!

Re: Date table with multi relationships

parry2k — Sat, 25 May 2019 13:11:12 GMT

gwright15 based on your input, you don't need 2nd date table, you are correct that you can have only one active relationship, it is if you are connecting same table on multiple dates, which you are already doing and using userrelationship function

You should able to have active relationship between your date table and fx rate table, not sure why you are not able to create active relationship between these two tables.

Re: Date table with multi relationships

gwright15 — Tue, 28 May 2019 10:54:56 GMT

Thanks for your help.

Does anything look wrong with this code, which would prevent these relationship working?

Exchange Rate = 
VAR
    RevisedDate = Filter( All(Date_Ex_Rate[Date]),
                            Date_Ex_Rate[Date] > Max ( Date_Ex_Rate[Date] ) -5 && Date_Ex_Rate[Date] <= Max(Date_Ex_Rate[Date] ) )

RETURN

Calculate (Exchange_Rates_GBP[Ex-Rate],
LASTNONBLANK(RevisedDate, Exchange_Rates_GBP[Ex-Rate]),USERELATIONSHIP('Date'[Date],Date_Ex-Rate[Date])
)

Deal_Value_Add_Time = Calculate(sum(deals[value]),USERELATIONSHIP('Date'[Date],deals[Add_Time_Local]),USERELATIONSHIP(deals[user_id],users[id]),NOT(deals[status]="deleted"))

Deal_Value_Add_Time_GBP = Sumx(deals,deals[Deal_Value_Add_Time]/Exchange_Rates_GBP[Exchange Rate])

Thanks again for your help!

Re: Date table with multi relationships

parry2k — Tue, 28 May 2019 14:54:55 GMT

gwright15 if you can share sample pbix/data will make it easy to look into it.