locked
USE RELATIONSHIP FUNCTION - DAX RRS feed

  • Question

  • Hi all,

    Can anyone tell me if the USERelationship function within DAX can be used to check for dates between start and end dates i.e. something similar to this?

    3 relationships between detail table and Calendar table in PowerPivot.

    Calendar.FullDateKey -> Order.InvoiceDate (Active Relationship)

    Calendar.FullDateKey -> Order.SubscriptionStartDate (InActive Relationship)

    Calendar.FullDateKey -> Order.SubscriptionEndDate (InActive Relationship)

    I want to split the data in power pivot via a fiscal month available from within my Calendar table but I want to do a distinctcount on the customers where start and end dates of the fiscal month are between the  Order.SubscriptionStartDate and Order.SubscriptionEndDate.

    I was trying the USERELATIONSHIP FUNCTION but obviously not correctly - can anyone share some knowledge on the same?

    Thanks,

    M

    Wednesday, May 2, 2012 5:05 PM

Answers

  • The following DAX formula will help you identify if you should count the row.  You may want to return the CustomerID instead of 1 and NULL instead of 0.  You should be able to distinctcount against this column.

    =if(Order.[InvoiceDate] <= Order.[SubscriptionEndDate], if(Order.[InvoiceDate] >=Order.[SubscriptionStartDate],1,0),0)


    Regards, Steve @dataonwheels http://www.dataonwheels.com

    • Marked as answer by Murtylad Tuesday, May 8, 2012 4:00 PM
    Wednesday, May 2, 2012 8:04 PM