Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Meraki_jeet
Frequent Visitor

Comparing Time Period on basis of Text field to get desired output. DAX or Measure?

Hi Everyone,

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

Meraki_jeet_0-1686049075577.png

 

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 

1 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

vbinbinyumsft_0-1686638893151.png

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.

View solution in original post

4 REPLIES 4
Meraki_jeet
Frequent Visitor

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.

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

vbinbinyumsft_0-1686638893151.png

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.

Thank you so much @v-binbinyu-msft. You made my day. It did worked. Thanks a lot for your support. Cheers!!!
v-binbinyu-msft
Community Support
Community Support

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors