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

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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] )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

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