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

Anonymous 

Try 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.