Forum Discussion

gwright15's avatar
gwright15
Icon for Helper I rankHelper I
7 years ago

Date table with multi relationships

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 o...
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

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.

  • gwright15's avatar
    gwright15
    Icon for Helper I rankHelper I
    7 years ago

    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!

     

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

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