Comparing Time Period on basis of Text field to get desired output. 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

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 

[Meraki_jeet]

Hi @v-binbinyu-msft ,
Thanks for your time. On basis of DIS_Scheduled Column i want to check if any resource doesnt have concurrency issue. For example take DIS_Scheduled = "SM_PROFILING_PRD" , If any resource has Scan_time suppose 12/26/2023 14:30 then no other resource should be scheduled 2 hrs before that i.e. 14:30 - 2 hrs = 12:30. So no other resource under DIS_Scheduled = "SM_PROFILING_PRD" should have Scan_time till 12:30 and if its present then that should be an issue. Below is my sample data where I have highlighted that scenario. I want to create a calculated column with output as "Issue" or "No issue". 

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

Note: For  DIS_scheduled = "MD_PROFILING_PRD"  no resources should have Scan_time upto 5 hrs before and if present then its issue. For LG_PROFILING_PRD 10hrs and for XL_PROFILING_PRD 24 hrs. 

Any ideas are welcomed please. If any transformation needed or something like that.

Thanks again for your time.

[v-binbinyu-msft · Accepted solution]

Hi @Meraki_jeet ,

Please try to create a new column with below dax formula:

Column =
VAR dis_schedule = [DIS_scheduled  ]
VAR cur_time = [Scan Time]
VAR tmp =
    FILTER ( 'Table', [DIS_scheduled  ] = dis_schedule )
VAR latest_time =
    MAXX ( tmp, [Scan Time] )
VAR tim_dif =
    DATEDIFF ( cur_time, latest_time, HOUR )
VAR _val =
    SWITCH (
        dis_schedule,
        "SM_PROFILING_PRD", IF ( tim_dif <= 2, "Issue", "No issue" ),
        "MD_PROFILING_PRD", IF ( tim_dif <= 5, "Issue", "No issue" ),
        "LG_PROFILING_PRD", IF ( tim_dif <= 10, "Issue", "No issue" ),
        "XL_PROFILING_PRD", IF ( tim_dif <= 24, "Issue", "No issue" )
    )
RETURN
    _val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
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-binbinyu-msft. You made my day. It did worked. Thanks a lot for your support. Cheers!!!

[v-binbinyu-msft]

Hi @Meraki_jeet ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu