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, I have an ID row, which will contain duplicates, I then have a column that displayed the amount of times that ID is found in the table, I have a fruit column, and I have a time column that would represent a modified timestamp, then I have the custom desired output column Id like.

 

I want the custom column to check for count of ID, if its equal to 1, then output equals fruit, 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, 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.

 

Please help. Ive spent so much time on this already and cant get it to work!. Thank you! 

 

 

 

 

  • smpa01's avatar
    smpa01
    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

     

     

1 Reply

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    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