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 ...
  • 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] )
)

     

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      In your two examples, the total should be the sum of all employees for that status.

      Example 1:

      Employee 1 = 7 + Employee 3 = 1 == Total = 8

      Example 2: 

      Employee 1 = 5 + Employee 2 = 3 + Employee 4 = 2 == Total = 10

       

      In addition in your first table, you can see that for the skill Quality Orientation you are returning two values for Not Verified. There are two values of Not Verified for that Skill but I only need the latest status and only counting that 1.

       

      In the example that I provided for Employee 1, I've highlighted below the skills that should be counted for Not Verified:

      Employee NameSkill NameStatusDate/Time
      Employee 1Active ListeningNot verified10/17/19 22:34
      Employee 1Adaptability & FlexibilityVerified8/21/19 12:20
      Employee 1Adaptability & FlexibilitySubmitted8/22/19 17:12
      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 verified9/24/19 20:27
      Employee 1Quality OrientationSubmitted9/24/19 20:27
      Employee 1Quality OrientationNot verified10/14/19 19:18
      Employee 1ResponsibleNot verified10/17/19 22:34
      Employee 1Senior / Lead Claim Processing TechnicianNot verified8/21/19 12:20
      Employee 1Senior / Lead Claim Processing TechnicianSubmitted10/15/19 1:39
      Employee 1Stress ToleranceNot verified10/17/19 22:34
      Employee 1System ArchitectureSubmitted9/24/19 20:27
      Employee 1System ArchitectureVerified10/14/19 19:18

      I also made an error before, the total for Not Verified should be 6. I counted an extra one because I had the dates sorted the wrong way. The most recent status needs to be Not Verified for it to count. You will notice the Skill Senior / Lead Claim Processing Technician is not counted now.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        tamerj1 checking to see if you saw my response.