Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
4 years ago
Solved

ISBETWEEN with Many to Many Relationship

Hello everyone, 

 

I'm looking to add a column that returns a 0 or 1 for if a certain record in the table has a date that falls inbetween a date range from another table.

 

For example this data, which the pbix file is attached, would generate a "1" for Index #s 0, 1 , 7 , 9 , 10 , 12 and 16 since they fall within one of the windows for their relative SchoolID.

 

PBIX Here 

Need Help
  • d_gosbell's avatar
    d_gosbell
    4 years ago

    You could do this with a calculated column like the following:

     

    = COUNTROWS(filter( SemesterTable,
        SemesterTable[SchoolID] = ShippingTable[SchoolID]
        && SemesterTable[WeekBeforeStart] <= ShippingTable[ShipDate]
        && SemesterTable[WeekAfterStart].[Date] >= ShippingTable[ShipDate])
    )+ 0

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    4 years ago

    You could do this with a calculated column like the following:

     

    = COUNTROWS(filter( SemesterTable,
        SemesterTable[SchoolID] = ShippingTable[SchoolID]
        && SemesterTable[WeekBeforeStart] <= ShippingTable[ShipDate]
        && SemesterTable[WeekAfterStart].[Date] >= ShippingTable[ShipDate])
    )+ 0
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    This calculated column formula works

    Column = CALCULATE(COUNTROWS(SemesterTable),FILTER(SemesterTable,SemesterTable[WeekBeforeStart]<=EARLIER(ShippingTable[ShipDate])&&SemesterTable[WeekAfterStart]>=EARLIER(ShippingTable[ShipDate])&&SemesterTable[SchoolID]=EARLIER(ShippingTable[SchoolID])))

    Hope this helps.