Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Harry1980
Helper I
Helper I

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

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


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.