Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Relate 2 date fields from 2 different tables

Setup:
I have 2 tables with a Many to Many relationship on a subscriptionid. This is necessary because in the first table, there can be multiple lines with the same subscriptionid due to the same subscriptionid having multiple start dates (This is due to how our system works when they buy more licenses, those licenses have their own start date). This table tells us how how many licenses they have and what dates those licenses start at.

The other table is a trend table, containing every personid and the subscriptionid they belong to. Since its a trend table, you can have multiple of the same person/subscriptionid because I am keeping track of changes to their accounts and licenses. Its complicated but necessary for how we do business. I keep track of each change by using a ValidFrom and ValidTo date field. 
Example: If a PersonId goes from being a Subscription Manager to an Admin, I track that with flags and what dates. When a change is found in the DB, the ValidTo line is updated and a new line is created with a new ValidFrom. 

The Problem
What I need to do is relate the ValidFrom and ValidFrom to the SubscriptionStartDate and SubscriptionEndDate in the first table. I Have the Subscription table related to the Trend table using the SubscriptionId.

What I tried was a Calculate where I perform my COUNT on the PersonId field (In Trend Table) and then in the filters, I want to say this:
ValidFrom > SubscriptionStartDate AND ValidTo > SubscriptionEndDate (This is because the "current" entries in the Trend table have a ValidTo of 12-31-9999). However, when I do this, it doesnt recognize those fields as valid.
Subscription Table
SubscriptionTableSubscriptionTable
TrendTable
TrendTableTrendTable
Relationship - its related on SubscriptionId

Related on SubscriptionIdRelated on SubscriptionId
Trying to do something like this
This is what Im trying to do in order to isolate the right rows.This is what Im trying to do in order to isolate the right rows.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:

VAR StartDate =
    SELECTEDVALUE ( Subscription[SubscriptionStartDate] )
VAR LicenseCreatedDate =
    MIN ( 'Trend'[LicenseCreatedDate] )
VAR SubId =
    SELECTEDVALUE ( Subscription[SubscriptionId] )
VAR _firstdupe =
    MAXX (
        FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ),
        Subscription[SubscriptionStartDate]
    )
RETURN
    IF (
        LicenseCreatedDate > StartDate
            && LicenseCreatedDate < _firstdupe,
        CALCULATE (
            COUNTX ( 'Trend', 'Trend'[PersonId] ),
            'Trend'[LicenseCreatedDate] > StartDate
                && 'Trend'[LicenseCreatedDate] < _firstdupe
        ),
        CALCULATE (
            COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] )
        )
    )



Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.

Screenshot 2023-01-13 151041.png

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User
Anonymous
Not applicable

That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:

VAR StartDate =
    SELECTEDVALUE ( Subscription[SubscriptionStartDate] )
VAR LicenseCreatedDate =
    MIN ( 'Trend'[LicenseCreatedDate] )
VAR SubId =
    SELECTEDVALUE ( Subscription[SubscriptionId] )
VAR _firstdupe =
    MAXX (
        FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ),
        Subscription[SubscriptionStartDate]
    )
RETURN
    IF (
        LicenseCreatedDate > StartDate
            && LicenseCreatedDate < _firstdupe,
        CALCULATE (
            COUNTX ( 'Trend', 'Trend'[PersonId] ),
            'Trend'[LicenseCreatedDate] > StartDate
                && 'Trend'[LicenseCreatedDate] < _firstdupe
        ),
        CALCULATE (
            COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] )
        )
    )



Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.

Screenshot 2023-01-13 151041.png

Anonymous
Not applicable

We ended up going a different route but I will mark my last one as a solution because it was close enough.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.