cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Harry1980
New Member

Measure changing value of an existing column

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

Harry1980_0-1619515596876.png

 

2 ACCEPTED SOLUTIONS

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

View solution in original post

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.

 

Picture3.png

View solution in original post

7 REPLIES 7
Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture3.png

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

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? 

Harry1980_0-1619519795087.png

 

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

View solution in original post

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

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.

 

Picture3.png

View solution in original post

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

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)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors