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
wes-shen-poal
Helper III
Helper III

Calculated column returning earlier datestamp of another column and a filter

Hi there,

 

I would like to create a calculated column named DwellTimeOutAdjusted in the 'container_dwell' data table

 

what I want the column to do for each 'N4 ActiveUnits'[UnitID] is to take the earlier datestamp of 'container_dwell'[DwellTimeOut] column and the  'N4 ActiveUnits'[SnapshotDate] filter

 

BUT

 

if the 'N4 ActiveUnits'[SnapshotDate] filter has a selected date that is earlier than the UnitID's 'container_dwell'[DwellTimeIn]

datestamp than the new column should take the 'container_dwell'[DwellTimeIn] datestamp instead

 

Below are the scenarios:

 

Picture1.jpg

 

Would someone be able to help me with the calculated column formula?

 

Thanks

Wes

 

ps. the 'container_dwell' data table and the 'N4 ActiveUnits' table are bidirectionally related on UnitID as primary key

1 ACCEPTED SOLUTION

Hi @wes-shen-poal,

You SnapshotDate column as a page filter, it will affect all report in the page. And you want to create a dynamic column based on the filter, we can't do this, please see the reason in this knowledage base. I have been considering use a measure, it there is one row after you select one date in filter, please try the following formula, and check if it works fine.

DwellTimeOut =
IF (
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    )
        <= CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate ]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    ),
    IF (
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        )
            >= CALCULATE (
                FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
                ALLSELECTED ( 'container_dwell' )
            ),
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
            ALLSELECTED ( 'container_dwell' )
        )
    )
)


Best Regards,
Angelia

View solution in original post

4 REPLIES 4
v-huizhn-msft
Employee
Employee

Hi @wes-shen-poal,

The relationship between 'container_dwell' data table and the 'N4 ActiveUnits' is one-to-one relationship? If it is, you can create a calculated column to get the 'N4 ActiveUnits'[SnapshotDate] in 'container_dwell' data table using related function. 

SnapshotDate filter=RELATED('N4 ActiveUnits'[SnapshotDate])


Then create another calculated column using the formula below to get what you desired.

DwellTimeOut =
IF (
    'container_dwell'[DwellTimeOut] <= 'container_dwell'[SnapshotDate filter],
    'container_dwell'[DwellTimeOut],
    IF (
        'container_dwell'[SnapshotDate filter] >= 'container_dwell'[DwellTimeIn],
        'container_dwell'[SnapshotDate filter],
        'container_dwell'[DwellTimeIn]
    )
)


If this desn't resolve your issue, please share more details for further analysis.

Best Regards,
Angelia

Hi @v-huizhn-msft

 

Thanks for the message. I don't think the calculated columns will work?

 

Sorry to confuse you.

 

I am using SnapshotDate column as a page filter.

 

So if we refer to screenshot below as an example:

 

For UnitID 2719892,

 

 

Capture.PNG

 

Because I've selected "31/10/2017 6:10:01PM" in the SnapshotDate filter, it would also be "31/10/2017 6:10:01PM" in new DwellTimeOutAdjusted column because my SnapshotDate filter is

(1) earlier than DwellTimeOut (="2/11/2017 2:38:56 PM") , AND,

(2) later than my DwellTimeIn (="28/10/2017 2:17:59 AM")

 

If I selected, e.g. "25/10/2017 4:50:01 AM" in my SnapshotDate filter then it would be "28/10/2017 2:17:59 AM" in my new DwellTimeOutAdjusted column because my SnapshotDate filter is

(1) earlier than my DwellTimeIn (="28/10/2017 2:17:59 AM")

 

If I selected, e.g. "3/11/2017 5:10:01 PM" in my SnapshotDate filter then it would be "2/11/2017 2:38:56 PM" in my new DwellTimeOutAdjusted column because my SnapshotDate filter is

(1) later than DwellTimeOut (="2/11/2017 2:38:56 PM")

 

So this new DwellTimeOutAdjusted column is dynamic, and significantly dependent on what is selected in the SnapshotDate filter to then decide which of three date/timestamps to take (DwellTimeIn, DwellTimeOut, SnapshotDate) as the DwellTimeOutAdjusted for each UnitID

 

Hope that makes more sense.

 

Regarding the table relationship:

I have a container_dwell 1-to-many N4 ActiveUnits relationship.

 

Thanks

Wes

Hi @wes-shen-poal,

You SnapshotDate column as a page filter, it will affect all report in the page. And you want to create a dynamic column based on the filter, we can't do this, please see the reason in this knowledage base. I have been considering use a measure, it there is one row after you select one date in filter, please try the following formula, and check if it works fine.

DwellTimeOut =
IF (
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    )
        <= CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate ]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
    CALCULATE (
        FIRSTNONBLANK (
            'container_dwell'[DwellTimeOut],
            'container_dwell'[DwellTimeOut]
        ),
        ALLSELECTED ( 'container_dwell' )
    ),
    IF (
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        )
            >= CALCULATE (
                FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
                ALLSELECTED ( 'container_dwell' )
            ),
        CALCULATE (
            FIRSTNONBLANK (
                'container_dwell'[SnapshotDate],
                'container_dwell'[SnapshotDate filter]
            ),
            ALLSELECTED ( 'container_dwell' )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'container_dwell'[DwellTimeIn], 'container_dwell'[DwellTimeIn] ),
            ALLSELECTED ( 'container_dwell' )
        )
    )
)


Best Regards,
Angelia

Hi @wes-shen-poal,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Thanks,
Angelia

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.