topic Re: Hello All, How to compare Date & Time using the text field. Dax or Measure? in DAX Commands and Tips

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

Meraki_jeet — Tue, 06 Jun 2023 12:35:47 GMT

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

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

Greg_Deckler — Tue, 06 Jun 2023 12:07:57 GMT

Meraki_jeet Can you post that sample data as text?

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

Meraki_jeet — Tue, 06 Jun 2023 12:32:11 GMT

Hi Greg_Deckler , Thanks. Please find data below,

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

Meraki_jeet — Wed, 07 Jun 2023 08:33:05 GMT

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

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

Meraki_jeet — Wed, 07 Jun 2023 11:29:01 GMT

Anyone can urgently help please with the solution?

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

v-jianboli-msft — Wed, 14 Jun 2023 05:27:24 GMT

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.

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

Meraki_jeet — Wed, 14 Jun 2023 20:46:01 GMT

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