Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Iterate through multiple columns to produce a value

I am trying to write a method in Power BI to select the last date in a column but iterates through other columns and then counts the number of occurences of that time stamp.

 

Provided below is an example. I have a table of hundreds of employees and every employee has several skills that can be in different status of completion and a time log of each time the status changes. When the status changes, the previous status keeps it time stamp and new value is created for the new status. With a slicer for Status, I want to have a card that shows the count of the last date over a time range, which a time slicer would be used to change the range. From the example below, if the slicer was selected as Status = Not Verified, the card should display 2. That is because it is only selecting the skills for each employee where Not Verified is the last time stamp. I highlighted those two values in red text show which two I am referring to. 

 

In other words, for every employee and each of their skills, count the last status for that skill. The Date/Time column seems to be the log of the most current status of that skill. I was researching how to apply a for loop for this but I could not figure that out with DAX.

 

Example table:

Employee NameSkill NameStatusDate/Time
Employee 1CodingNot Verified2/23/23 10:00:00
Employee 1CodingSubmitted2/23/23 10:30:00
Employee 1WritingNot Verified2/23/23 10:00:00
Employee 2CodingNot Verified2/23/23 10:00:00
Employee 2CodingSubmitted2/23/23 10:15:00
Employee 2 CodingVerified2/23/23 11:00:00
Employee 3CommunicationNot Verified2/23/23 12:00:00
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    Do you mean like this?

    Skill Count = 
CALCULATE ( 
    SUMX ( 
        SUMMARIZE ( 'Table', 'Table'[Employee Name], 'Table'[Skill Name], 'Table'[Status] ),
        IF ( 
            CALCULATE ( MAX ( 'Table'[Date/Time] ) ) =
                CALCULATE ( MAX ( 'Table'[Date/Time] ), ALL ( 'Table'[Date/Time], 'Table'[Status] ), ALL ( 'Status' ) ),
            1
        )
    ),
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

     

16 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 
    Please provide more examples

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      In the table below Employee 1 has 11 unique Skill Names, which you can see has multiple Status per Skill Name and some have repeated values from changing of those statuses. With a slicer selected as Status = Not Verified, the total should show 7 by returning back 1 Status with the Status as Not Verified per Skill Name. Because that Status can be multiple times per Skill Name, the latest Date/Time should be selected. 

       

      Employee NameSkill NameStatusDate/Time
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1Adaptability & FlexibilityVerified8/21/19 12:20
      Employee 1Managing WorkVerified8/21/19 12:20
      Employee 1Senior / Lead Claim Processing TechnicianNot verified8/21/19 12:20
      Employee 1Adaptability & FlexibilitySubmitted8/22/19 17:12
      Employee 1Quality OrientationNot verified9/24/19 20:27
      Employee 1Initiating ActionSubmitted9/24/19 20:27
      Employee 1Quality OrientationSubmitted9/24/19 20:27
      Employee 1System ArchitectureSubmitted9/24/19 20:27
      Employee 2Quality OrientationSubmitted9/24/19 20:28
      Employee 2Senior / Lead Claim Processing TechnicianSubmitted9/24/19 20:28
      Employee 2System ArchitectureSubmitted9/24/19 20:28
      Employee 1System ArchitectureVerified10/14/19 19:18
      Employee 1Quality OrientationNot verified10/14/19 19:18
      Employee 1Senior / Lead Claim Processing TechnicianSubmitted10/15/19 1:39
      Employee 3Quality OrientationNot verified10/16/19 16:21
      Employee 3System ArchitectureVerified10/16/19 16:21
      Employee 1Active ListeningNot verified10/17/19 22:34
      Employee 1ResponsibleNot verified10/17/19 22:34
      Employee 1Stress ToleranceNot verified10/17/19 22:34
      Employee 1High School Diploma or equivalentNot verified10/23/19 19:21
      Employee 4InfrastructureSubmitted11/14/19 17:35
      Employee 4Presenting & Communicating InformationSubmitted11/14/19 17:35

       

      Here is the same table but with only Employee 1 and sorted by Skill Name then Date/Time to more easily understand the example above. The table above was sorted by Date/Time as it would normally appear.

      Employee NameSkill NameStatusDate/Time
      Employee 1Active ListeningNot verified10/17/19 22:34
      Employee 1Adaptability & FlexibilitySubmitted8/22/19 17:12
      Employee 1Adaptability & FlexibilityVerified8/21/19 12:20
      Employee 1High School Diploma or equivalentNot verified10/23/19 19:21
      Employee 1Initiating ActionSubmitted9/24/19 20:27
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1InterpersonalNot verified8/21/19 12:17
      Employee 1Managing WorkVerified8/21/19 12:20
      Employee 1Quality OrientationNot verified10/14/19 19:18
      Employee 1Quality OrientationSubmitted9/24/19 20:27
      Employee 1Quality OrientationNot verified9/24/19 20:27
      Employee 1ResponsibleNot verified10/17/19 22:34
      Employee 1Senior / Lead Claim Processing TechnicianSubmitted10/15/19 1:39
      Employee 1Senior / Lead Claim Processing TechnicianNot verified8/21/19 12:20
      Employee 1Stress ToleranceNot verified10/17/19 22:34
      Employee 1System ArchitectureVerified10/14/19 19:18
      Employee 1System ArchitectureSubmitted9/24/19 20:27
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 
        Ok, that does not match with your initial description. Kindly provide your red markups on below proposal's screenshots.