Forum Discussion

Sri_phani's avatar
Sri_phani
Helper III
2 years ago
Solved

Get the First activity based on date column

Hi, 

 

I have been trying this for a long time, but I haven't been able to do this.  please help me with this. 

 

Requirement: I have to show agents today's first break period in a Card visual.  
Tables in Dataset - Activeagents, Verint table. 

Relationship: Activeagents to Verint Table (1 to Many). 

Condition - The page has an Employee Name slicer, for the selected employee name, based on shift date filter today's date and filter Break, then based on activity start time first minium value to show the Activity timesbetween(result is like 04:00 PM - 04:15 PM IST)

Here is the sample data of single employee : https://docs.google.com/spreadsheets/d/17wFnZqJb6fDiMZcek_L3k0EipAGssLYRgpGKKMXeXRs/edit?usp=sharing 

(I want the value which is highlighted in Yellow and Red in Card visual)


Here is the DAX code which I am using = 

Firstbreak =
VAR TodayDate = TODAY()
VAR SelectedEmployee = SELECTEDVALUE(ActiveAgentsFiltered[Employee Name])

// Step 1: Filtered Table
VAR Filtered =
    FILTER(
        Verint,
        Verint[Shift Date] >= TodayDate &&
        Verint[Shift Date] < TodayDate + 1 &&
        CONTAINSSTRING(Verint[Activity Name], "Break") &&
        Verint[Employee Name as per IROZ] = SelectedEmployee
    )

// Step 2: Minimum Start Time
VAR MinimumStartTime =
    CALCULATE(
        MIN(Verint[Activity Start time]),
        Filtered
    )

// Step 3: Corresponding Activity Time Between
VAR ActivityTime =
    CALCULATE(
        FIRSTNONBLANK(Verint[Activity Timebetween], 1),
        Verint[Activity Start time] = MinimumStartTime,
        Verint[Employee Name as per IROZ] = SelectedEmployee
    )
// Return the Result
RETURN
ActivityTime

 

Appreciate your help. 

 

DAX
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Sri_phani Try this. PBIX is attached below signature.

    Measure = 
    VAR __Emp = MAX('Table'[Employee Name as per IROZ])
    VAR __Table = FILTER( 'Table', [Extracted Activity Name] = "Break_Unpaid" )
    VAR __MaxBreakStart = MAXX( __Table, [Activity start IST/EET] )
    VAR __MaxBreakEnd = MAXX( FILTER( 'Table', [Activity start IST/EET] = __MaxBreakStart ), [Activity end IST/EET] )
    VAR __BreakStartTime = __MaxBreakStart - TRUNC( __MaxBreakStart )
    VAR __BreakEndTime = __MaxBreakEnd - TRUNC( __MaxBreakEnd )
    VAR __Result = FORMAT(__BreakStartTime, "Medium time" ) & " - " & FORMAT( __MaxBreakEnd, "Medium time" )
RETURN
    __Result

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    hi Sri_phani ,

     

    as per the data available in the mentioned link the minimum time for break on 26th Jul should be 12.45 PM.

    After considering this, if I proceed then I have done the following steps

    Filter the table with respect to Break_Unpaid

    Calculating first break

     

    Please check visualization

    Now if you need the yellow/red highlighted time as first break then either max function or distinctcount function (if there are more than 2 break periods available for an employee per day) needs to be used.
    Using max function

    Using Distinctcount function

    Visualization can be done in the similar way.

    If this solves your problem then accept the same as your solution.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    hi Sri_phani ,

     

    as per the data available in the mentioned link the minimum time for break on 26th Jul should be 12.45 PM.

    After considering this, if I proceed then I have done the following steps

    Filter the table with respect to Break_Unpaid

    Calculating first break

     

    Please check visualization

    Now if you need the yellow/red highlighted time as first break then either max function or distinctcount function (if there are more than 2 break periods available for an employee per day) needs to be used.
    Using max function

    Using Distinctcount function

    Visualization can be done in the similar way.

    If this solves your problem then accept the same as your solution.

    • Sri_phani's avatar
      Sri_phani
      Helper III
      2 years ago

      You solution as well worked for me. Thank you Rakesh.!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Sri_phani if it works then it is requested you to accept the same as solution. It will be helpful for me too.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Sri_phani Try this. PBIX is attached below signature.

    Measure = 
    VAR __Emp = MAX('Table'[Employee Name as per IROZ])
    VAR __Table = FILTER( 'Table', [Extracted Activity Name] = "Break_Unpaid" )
    VAR __MaxBreakStart = MAXX( __Table, [Activity start IST/EET] )
    VAR __MaxBreakEnd = MAXX( FILTER( 'Table', [Activity start IST/EET] = __MaxBreakStart ), [Activity end IST/EET] )
    VAR __BreakStartTime = __MaxBreakStart - TRUNC( __MaxBreakStart )
    VAR __BreakEndTime = __MaxBreakEnd - TRUNC( __MaxBreakEnd )
    VAR __Result = FORMAT(__BreakStartTime, "Medium time" ) & " - " & FORMAT( __MaxBreakEnd, "Medium time" )
RETURN
    __Result