Forum Discussion

chemota's avatar
chemota
New Member
2 years ago

chatgpt cannot help me! (return value from another row that has another variable in common)

I need a DAX formula for Power BI that creates a new column (the new column will be called "columna") in a table.

 

The table is named: "brickCrowd17 transactions_2"

The new column will be called "columna"

This table has 3 columns that interest me: the "lemonway_id" column, the "user_id" column, and the "type" column.

In the case that the value of the "type" column is 20 or 21, it has to search for the "lemonway_id" column.

And if the value of "lemonway_id" is repeated in the table, then the function has to give me the value of the "user_id" column from the other row.

If it doesn't find it, it should leave it blank.

Need Help

3 Replies

  • j_ocean's avatar
    j_ocean
    Helper V
    2 years ago
    1. Split off an un-loaded copy of the table
    2. Filter to 20/21
    3. Drop all but lemonway_id and user_id
    4. Remove duplicates table-wide
    5. Group-by lemonway_id with an "average" on user_id, and a row count
    6. Change the "average" code to Text.Combine to get a concatenated list of users in that cell.
    7. Filter out row count 1 (the ones where there is no repeat)
    8. Merge this table back into a copy of your original matching on lemonway_id and expanding out your concatenated list of users.

    Conversely in power bi you can use the original table to supply a matrix with lemonway_id and user_id as rows.

  • chemota's avatar
    chemota
    New Member
    2 years ago

    thanks I understand all the steps, but in the last one, which function should i use to tell to grab the one that doesn´t match the original

    this is the original table:

    "lemonway_id" "user_id"  "type" 
    123456, 2542,20
    123456,9584,21

    this is the final outcome that I need:

    "lemonway_id" "user_id"  "type" "columna"
    123456, 2542,20,9584
    123456,9584,21,2542

     

    • j_ocean's avatar
      j_ocean
      Helper V
      2 years ago

      You would wind up with a duplicate, yes--userid in one column, and that same one again in "all user ids" in the other. I think you mean you want the second column to be "all OTHER userids"?

       

      You can do the merge earlier, before the group-by, and add/filter on a logical column to drop rows with matching userid values. Then do the same group-by with Text.Combine.

       

      Be aware this will duplicate values so if you have a numerical column in there be careful not to sum it in the group-by.