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] ) )
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.
Community ChampionAnonymous
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
)
)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
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 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.
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" ) 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?
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.