Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Double Relationship Between Two Tables

Hi Power BI Community!   I finally managed to create the waterfall visual I wanted using the solution tagged in below, which I am incredibly excited about. Unfortunately, I have now come across ano...
Need Help
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi Anonymous ,

     

    You may create an active relationship on 'Date table'[Date] and 'Fact table'[Date1] ,  and then create an another inactive relationship on 'Date table'[Date] and 'Fact table'[Date2] . Once you need the relationship about 'fact table'[Date2], just to create column or measure using USERELATIONSHIP like DAX below.   ( Note that the [Date1] could be the frequently used one. In your scenario, the [Date1] may be [Open Date], and the [Date2] may be [Close Date] ),

     

    Total Sales= CALCULATE (SUM('fact table'[Sales]) ),USERELATIONSHIP ( Date[Date], 'fact table'[Date2] ))

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-xicai's avatar
v-xicai
Community Support
5 years ago

Hi Anonymous ,

 

You may create an active relationship on 'Date table'[Date] and 'Fact table'[Date1] ,  and then create an another inactive relationship on 'Date table'[Date] and 'Fact table'[Date2] . Once you need the relationship about 'fact table'[Date2], just to create column or measure using USERELATIONSHIP like DAX below.   ( Note that the [Date1] could be the frequently used one. In your scenario, the [Date1] may be [Open Date], and the [Date2] may be [Close Date] ),

 

Total Sales= CALCULATE (SUM('fact table'[Sales]) ),USERELATIONSHIP ( Date[Date], 'fact table'[Date2] ))

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.