Forum Discussion

mikemarr3's avatar
mikemarr3
Frequent Visitor
2 years ago
Solved

Returning Values from a column, based on their being duplicate values in another column

Hey there,   Im trying to create a custom column that would display a value from another column, based on the fact that I found duplicate values in a 3rd column.   In this below sample data set, ...
  • smpa01's avatar
    2 years ago

    mikemarr3  you can do a measure like this

    Measure = 
    VAR tblOne =
        ALL ( 'Table' )
    VAR tblTwo =
        VALUES ( 'Table'[id] )
    
    //check for count of ID -- count of id by unique id    
    VAR countID =
        CALCULATE ( COUNT ( 'Table'[id] ), tblOne, tblTwo ) 
    VAR time =
        MAX ( 'Table'[time] )
    //minTime by ID    
    VAR minTime =
        CALCULATE ( MIN ( 'Table'[time] ), tblOne, tblTwo )
    //maxTime by ID    
    VAR mxTime =
        CALCULATE ( MAX ( 'Table'[time] ), tblOne, tblTwo )
    // temp table of all fruits by ID    
    VAR _right =
        SELECTCOLUMNS (
            SUMMARIZE (
                FILTER (
                    tblOne,
                    'Table'[id] = CALCULATE ( MAX ( 'Table'[id] ), tblOne, tblTwo )
                ),
                'Table'[fruit]
            ),
            "fruit", 'Table'[fruit] & ""
        )
    // temp table for explicit checking    
    VAR _left =
        DATATABLE ( "fruit", STRING, { { "banana" }, { "apple" } } )
    //checks if fruit by id is either banana or apple    
    VAR _join =
        NATURALINNERJOIN ( _left, _right )
    // count the result of joins to determine if there is any zero match and remianing rows for that ID the output would be blank.   
    // If the group of fruits from that group of IDs does not contain apple or banana, then display the fruit from the most recent modified record, and the others will be blank. 
    VAR rowCount =
        COUNTROWS ( _join )
    // if its greater than 1, then find all the rows with the ID, and check the fruit column for the type of fruit, if the type of fruit is equal to banana or apple, then output is either banana or apple    
    VAR ternary =
        IF (
            rowCount >= 1
                && time == minTime,
            MAX ( 'Table'[fruit] ),
            IF ( rowCount == BLANK () && time == mxTime, MAX ( 'Table'[fruit] ) )
        )  
    VAR final =
        IF ( countID = 1, MAX ( 'Table'[fruit] ), /*if its equal to 1, then output equals fruit */ ternary )
    RETURN
        final