Measure changing value of an existing column

[Harry1980]

Hi everybody,

 I have started working with PBI and struggle with solving a little issue I have. We have a material A15148 which has replaced  2 others, namely A11403 and A14737 (see column B (material cat 2)). You can see  in column C (Status) that the current material does not necessarily have the same status then the old/replaced materials: In the example below the 2 predecessors have status AA whereas the current material A15148 has AU. I would like to create a measure assuring that also A14403 & A14737 will show AU (see Column E). Or in other words that the status is always identical (I.e. always apply the status of the active material A15148)

Worth to mention that I am not owning the database and cannot simply add  a column. If this shall work I would need to create a measure. Do you see there any smart solution for this?

Your help is highly appreciated.

Thank you in advance

Harry

[Jihwan_Kim · Accepted solution]

Hi, @Harry1980 

Thank you for your explanation below.

I still quite do not understand how to define the newest material, but please check the below measure whether it suits your real case.

Customized Status =

VAR newestmaterial = MAX('Table'[Material Cat 1])

return

CALCULATE (

SELECTEDVALUE ( 'Table'[Status] ),

FILTER ( ALL ( 'Table' ), 'Table'[Material Cat 2] = newestmaterial )

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim]

Hi, @Harry1980 

Please check the below picture and the sample pbix file's link down below.

I could not know how to define the old materials and the new material, so I simply wrote the measure like below.

If you can tell me more about how to define the old and the new, then perhaps I can try to write a more accurate measure.

Customized Status =

CALCULATE (

SELECTEDVALUE ( 'Table'[Status] ),

FILTER ( ALL ( 'Table' ), 'Table'[Material Cat 2] = "A15148" )

)

https://www.dropbox.com/s/ynzrfb8vwrw34pv/harryv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Harry1980]

Hi @Jihwan_Kim, when I am checking your code "Table'[Material Cat 2] = "A15148" )"  I need to mention that A15148 was just one example where an actual item has predecessors. In the underlying table there are hundreds of materials which have predecessors. Also for those I would like to apply the logic described above and customize the status. How can I achieve that? 

[Jihwan_Kim · Accepted solution]

Hi, @Harry1980 

Thank you for your explanation below.

I still quite do not understand how to define the newest material, but please check the below measure whether it suits your real case.

Customized Status =

VAR newestmaterial = MAX('Table'[Material Cat 1])

return

CALCULATE (

SELECTEDVALUE ( 'Table'[Status] ),

FILTER ( ALL ( 'Table' ), 'Table'[Material Cat 2] = newestmaterial )

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Harry1980]

Hi, please have a look into message #3. There I have illustrated the logic. Your support is much appreciated

[Jihwan_Kim · Accepted solution]

Sorry, I quite do not understand your point. Have you tried my last measure?

It shows the below anyway.

I think I am missing something.

If you have another sample that the below measure does not suit, please share.

[Harry1980]

Hi @Jihwan_Kim: fantastic it is working perfectly fine. Thank you very much for help. It is highly appreciated!!!!!!!😀

[Harry1980]

Hi @Jihwan_Kim,  I apologize that I was not precise enough on this. The logic to identify new materials: Whenever value in column material cat1 = value in column material cat2, then this is the new material. If column material cat1 <> value in column material cat2 then those are the replaced material (old materials)