Forum Discussion
Iterate through multiple columns to produce a value
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] ) )
Community ChampionAnonymous
Ok, that does not match with your initial description. Kindly provide your red markups on below proposal's screenshots.
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 Name | Skill Name | Status | Date/Time |
| Employee 1 | Active Listening | Not verified | 10/17/19 22:34 |
| Employee 1 | Adaptability & Flexibility | Verified | 8/21/19 12:20 |
| Employee 1 | Adaptability & Flexibility | Submitted | 8/22/19 17:12 |
| Employee 1 | High School Diploma or equivalent | Not verified | 10/23/19 19:21 |
| Employee 1 | Initiating Action | Submitted | 9/24/19 20:27 |
| Employee 1 | Interpersonal | Not verified | 8/21/19 12:17 |
| Employee 1 | Interpersonal | Not verified | 8/21/19 12:17 |
| Employee 1 | Interpersonal | Not verified | 8/21/19 12:17 |
| Employee 1 | Managing Work | Verified | 8/21/19 12:20 |
| Employee 1 | Quality Orientation | Not verified | 9/24/19 20:27 |
| Employee 1 | Quality Orientation | Submitted | 9/24/19 20:27 |
| Employee 1 | Quality Orientation | Not verified | 10/14/19 19:18 |
| Employee 1 | Responsible | Not verified | 10/17/19 22:34 |
| Employee 1 | Senior / Lead Claim Processing Technician | Not verified | 8/21/19 12:20 |
| Employee 1 | Senior / Lead Claim Processing Technician | Submitted | 10/15/19 1:39 |
| Employee 1 | Stress Tolerance | Not verified | 10/17/19 22:34 |
| Employee 1 | System Architecture | Submitted | 9/24/19 20:27 |
| Employee 1 | System Architecture | Verified | 10/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.
tamerj1 checking to see if you saw my response.
- tamerj1
Anonymous
Sorry, I did not notice. Please refer to attached sample file.Skill Count = 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] ) ), 1 ) )tamerj1 Thank you for your quick response. The values for the Not Verified are not accurate. Please see the last post I shared with values in red.
"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."Based on your method, it would somehow be Max date but for a given skill. Since this is a continuous log based on skill status, the skill will be repeated for every updated skill status, and thats why I need the last time for that skill = that is the current status of the skill.