Iterate through multiple columns to produce a value

[Anonymous]

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 Name	Skill Name	Status	Date/Time
Employee 1	Coding	Not Verified	2/23/23 10:00:00
Employee 1	Coding	Submitted	2/23/23 10:30:00
Employee 1	Writing	Not Verified	2/23/23 10:00:00
Employee 2	Coding	Not Verified	2/23/23 10:00:00
Employee 2	Coding	Submitted	2/23/23 10:15:00
Employee 2	Coding	Verified	2/23/23 11:00:00
Employee 3	Communication	Not Verified	2/23/23 12:00:00

[tamerj1]

Hi @Anonymous 
Please provide more examples

[Anonymous]

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 Name	Skill Name	Status	Date/Time
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	Adaptability & Flexibility	Verified	8/21/19 12:20
Employee 1	Managing Work	Verified	8/21/19 12:20
Employee 1	Senior / Lead Claim Processing Technician	Not verified	8/21/19 12:20
Employee 1	Adaptability & Flexibility	Submitted	8/22/19 17:12
Employee 1	Quality Orientation	Not verified	9/24/19 20:27
Employee 1	Initiating Action	Submitted	9/24/19 20:27
Employee 1	Quality Orientation	Submitted	9/24/19 20:27
Employee 1	System Architecture	Submitted	9/24/19 20:27
Employee 2	Quality Orientation	Submitted	9/24/19 20:28
Employee 2	Senior / Lead Claim Processing Technician	Submitted	9/24/19 20:28
Employee 2	System Architecture	Submitted	9/24/19 20:28
Employee 1	System Architecture	Verified	10/14/19 19:18
Employee 1	Quality Orientation	Not verified	10/14/19 19:18
Employee 1	Senior / Lead Claim Processing Technician	Submitted	10/15/19 1:39
Employee 3	Quality Orientation	Not verified	10/16/19 16:21
Employee 3	System Architecture	Verified	10/16/19 16:21
Employee 1	Active Listening	Not verified	10/17/19 22:34
Employee 1	Responsible	Not verified	10/17/19 22:34
Employee 1	Stress Tolerance	Not verified	10/17/19 22:34
Employee 1	High School Diploma or equivalent	Not verified	10/23/19 19:21
Employee 4	Infrastructure	Submitted	11/14/19 17:35
Employee 4	Presenting & Communicating Information	Submitted	11/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 Name	Skill Name	Status	Date/Time
Employee 1	Active Listening	Not verified	10/17/19 22:34
Employee 1	Adaptability & Flexibility	Submitted	8/22/19 17:12
Employee 1	Adaptability & Flexibility	Verified	8/21/19 12:20
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	10/14/19 19:18
Employee 1	Quality Orientation	Submitted	9/24/19 20:27
Employee 1	Quality Orientation	Not verified	9/24/19 20:27
Employee 1	Responsible	Not verified	10/17/19 22:34
Employee 1	Senior / Lead Claim Processing Technician	Submitted	10/15/19 1:39
Employee 1	Senior / Lead Claim Processing Technician	Not verified	8/21/19 12:20
Employee 1	Stress Tolerance	Not verified	10/17/19 22:34
Employee 1	System Architecture	Verified	10/14/19 19:18
Employee 1	System Architecture	Submitted	9/24/19 20:27

[tamerj1]

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

[Anonymous]

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.

[Anonymous]

@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
    )
)

[Anonymous]

@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. 

[tamerj1]

@Anonymous 
This would require a status dim table. 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], 'Table'[Status] ), ALL ( 'Status' ) ),
        1
    )
)

[Anonymous]

Thank you @tamerj1 . This was very helpful. I am a little unsure as to why a separate dim table would be required.

If I wanted to modify this measure to filter the skill status as Not verified, how could this be update? The ALL function is throwing me off, which is intended to clear all filters.

I want to create a measure for each skill status and plot that data across a line chart, showcasing the different status changing over time. 

[tamerj1 · Accepted solution]

@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] )
)

[Anonymous]

@tamerj1 This looks good, but I am confused about the additional dim tables. Why would I need both the date and status tables if those values are already within the table with Employee Name and Skill Status?

[tamerj1]

@Anonymous 

It could work. Actually I tried a solution myself without dim and it worked to some extent. This is related to the dax engine optimization properties that makes the utilization of dim tables easier to obtain the required results. Long story short try to rely more on dim tables in your data models. 

[Anonymous]

Thank you! One last question, if I wanted to take the values that are calculated for the skill count measure and display the totals for each skill status is separate cards, is there a way to filter those out individually? The result would look like this below (those values are incorrect and are simply countrows for the skill status, but thats how I am trying to display the correct skill count values).

[tamerj1]

@Anonymous 

Try 

Skill Count Submitted =
CALCULATE ( [Skill Count], Status[Status] = "Submitted" )

[Anonymous]

The sum of each card (with the measure above) is totaling the sum of all status together, which checks out. However in the line chart when I count the value for each month, it is much greater.

The card all the way to the right is the total of the 3 other cards. Counting the values in the tooltip across each month, the values shown for Verified (and the others) is greater than what is displaying in the cards. All of these should match. Any idea why it is not? 

[Anonymous]

@tamerj1 I tested this a bit further and the reason is that the Cards are not being updated with the Date slicer whereas the line chart is. The Cards should be changing as the date changes.