Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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
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
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,
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |