create a new column based on two groups

[william117]

Hello I need your help, I want to transfer information that is in one column to another but placing the data with others that do have, the idea is to compare them, currently it is all in one column, I have the movement group as move_id and the products as product_id , I find sense that from there I can identify them.

I tried this DAX but it doesn't show desired values.

CANT PT = MAXX(FILTER(Hoja1,Hoja1[move_id]=
EARLIER(Hoja1[move_id])&&NOT(ISBLANK(Hoja1[out]))),Hoja1[out])

I tried this DAX but it didn't work.

CANT PT = MAXX(FILTER(Hoja1,Hoja1[move_id]=
EARLIER(Hoja1[move_id])&&FILTER(Hoja1,Hoja1[name_id])=EARLIER(Hoja1[name_id])&&NOT(ISBLANK(Hoja1[out]))),Hoja1[out])

In this image I show how the table is currently and the expected final result.

Thanks for your time

[v-chenwuz-msft · Accepted solution]

Hi @william117 

Try this measure:

Measure =
IF(
    SELECTEDVALUE( 'Table'[out] ) = 0,
    MAXX( FILTER( ALL( 'Table' ), [name_id] = MAX( 'Table'[name_id] ) ), [out] ),
    0
)

If you want to create a column then via following expression

Column =
IF(
    [out] = 0,
    MAXX( FILTER( 'Table', [name_id] = EARLIER( 'Table'[name_id] ) ), [out] ),
    0
)

The difference between measures and columns is that measures are calculated based on the context of the filter, whereas the results of the new column calculation are calculated and stored immediately. The following article may be of help to you.

Calculated Columns and Measures in DAX

I put my pbix file in the attachment you can reference. Plaese let me know if I misundestood you.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@william117 the best approach would be to unpivot the data, in transform data -> select all the columns except in and out -> right-click and then unpivot other columns, you will get two columns attribute and value, rename these if you want, close and apply

and now create a matrix visual, add columns on the rows, add attribute on column and value on the values which will be sum and you will see everything next to each other.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡