Hello All, How to compare Date & Time using the text field. Dax or Measure?

[Meraki_jeet]

Hi Everyone,

Could someone please help me out with DAX for below as I am stuck in middle. Below is my sample data

                      Resource Name                                  DIS_scheduled                       Scan Time
TEST_CONCURRENT_SCANS_ORA_SM_8       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_7       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_6       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_5       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_4      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_3      MD_PROFILING_PRD           12/27/2023 13:30
TEST_CONCURRENT_SCANS_ORA_MD_2      MD_PROFILING_PRD           12/27/2023 15:00
TEST_CONCURRENT_SCANS_ORA_MD_1      MD_PROFILING_PRD           12/27/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3       LG_PROFILING_PRD             12/28/2023 13:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD              12/28/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD              12/28/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD              12/29/2023 10:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD              12/30/2023 7:00
TEST_CONCURRENT_SCANS_ORA_SM_8     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_7     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_6     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_5     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_4     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_3     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_2     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_1     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD            12/31/2023 7:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD            12/31/2023 7:00

so here 

>> any resource should not be scheduled(i.e. Scan_time column above) if there are resources already scheduled upto 2 hrs before for DIS_scheduled = "SM_PROFILING_PRD"

>> any resource should not be scheduled if there are resources already scheduled upto 5 hrs before for DIS_scheduled = "MD_PROFILING_PRD"

>> any resource should not be scheduled if there are resources already scheduled upto 10 hrs before for DIS_scheduled = "LG_PROFILING_PRD"
>> any resource should not be scheduled if there are resources already scheduled upto 24 hrs before for DIS_scheduled = "XL_PROFILING_PRD"

So for Example, For DIS_scheduled = "SM_PROFILING_PRD", if  2 Resources have Scan_time on 12/31/2023 14:30 but there are already  resources having Scan_time at 12/31/2023 13:00 which is an issue as no resource should be scheduled 2hrs before

I have to use Scan_Time column to check above conditions and create new column with output as "Issue" or "No issue"
Please let me know if more data is required.

Thanks 

[v-jianboli-msft · Accepted solution]

Hi @Meraki_jeet ,

Please try:

Column = 
var _a = SWITCH([DIS_scheduled],
                "SM_PROFILING_PRD",[Scan Time]-TIME(2,0,0),
                "MD_PROFILING_PRD",[Scan Time]-TIME(5,0,0),
                "LG_PROFILING_PRD",[Scan Time]-TIME(10,0,0),
                "XL_PROFILING_PRD",[Scan Time]-TIME(24,0,0))
var _c = COUNTX(FILTER('Table',[Scan Time]>=_a&&[Scan Time]<EARLIER('Table'[Scan Time])),[Resource Name])
return IF(_c<>BLANK(),"Issue","No issue")

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Meraki_jeet]

Thank you so much @v-jianboli-msft . Cheers!! It did worked.

[Meraki_jeet]

Anyone can urgently  help please with the solution?

[Meraki_jeet]

Hi @Greg_Deckler , Do you have any idea on how I can do the above. Please let me know if any

[Meraki_jeet]

Hi @Greg_Deckler  , Thanks.  Please find  data below,

                      Resource Name                                  DIS_scheduled                       Scan Time
TEST_CONCURRENT_SCANS_ORA_SM_8       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_7       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_6       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_5       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_4      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_3      MD_PROFILING_PRD           12/27/2023 13:30
TEST_CONCURRENT_SCANS_ORA_MD_2      MD_PROFILING_PRD           12/27/2023 15:00
TEST_CONCURRENT_SCANS_ORA_MD_1      MD_PROFILING_PRD           12/27/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3       LG_PROFILING_PRD             12/28/2023 13:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD              12/28/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD              12/28/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD              12/29/2023 10:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD              12/30/2023 7:00
TEST_CONCURRENT_SCANS_ORA_SM_8     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_7     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_6     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_5     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_4     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_3     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_2     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_1     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD            12/31/2023 7:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD            12/31/2023 7:00

[Greg_Deckler]

@Meraki_jeet Can you post that sample data as text?