Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a True() False() columns to identify when specific fields change

Hi, my manager wants a field that identifies when something changes in a data set. I want to be able to create a field with some kind of mark or true/false or something like that when if something changes, i can capture that change.

For example, here is a subset of data that I want to apply this to.

What I want to do is the following.
For every time the formatted ID or the Iteration Changes, I want a True()/False() field whenever the changes occur.  I will be using the results in my filtering, so having the ability to mark the differences will be very helpful.  

Thanks, Steven

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Anonymous ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Flag1 =
var _Group1Index1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[Group1])
var _Group1Index2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1),[Group1])
var _Groupall1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])&&'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])),[Group2])
var _Groupall2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1&&'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])),[Group2])
return
IF(
    _Group1Index1<>_Group1Index2,"True",
IF(
    _Groupall1<>_Groupall2,"False",
    BLANK()))

    2. Result:

     

    Whether the screenshot above meets your expectations

     

    Best Regards,

    Liu Yang

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

     

    Here are the steps you can follow：

    1. In Power Query -- Add Column – Index Column – From 1.

    2. Create calculated column.

    Group1 =
MINX(FILTER(ALL('Table'),
'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])),[Index])
    Group2 =
MINX(FILTER(ALL('Table'),
'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])&&'Table'[Iteation]=EARLIER('Table'[Iteation])),[Index])
    Flag =
var _Group1Index1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[Group1])
var _Group1Index2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1),[Group1])
var _Group2Index1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[Group2])
var _Group2Index2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1),[Group2])
return
IF(
    _Group1Index1<>_Group1Index2,FALSE(),
    IF(
    _Group2Index1<>_Group2Index2,FALSE(),TRUE()))

    3. Result:

     

    Best Regards,

    Liu Yang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you very much! This was helpful and helped me resolve the issue.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Flag1 =
var _Group1Index1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[Group1])
var _Group1Index2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1),[Group1])
var _Groupall1=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])&&'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])),[Group2])
var _Groupall2=SUMX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1&&'Table'[Formatted ID]=EARLIER('Table'[Formatted ID])),[Group2])
return
IF(
    _Group1Index1<>_Group1Index2,"True",
IF(
    _Groupall1<>_Groupall2,"False",
    BLANK()))

    2. Result:

     

    Whether the screenshot above meets your expectations

     

    Best Regards,

    Liu Yang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks very much for this suggestion.  However it doesn't seem to be working.
    I downloaded your PBIX and set it up. I think I duplicated what you have in your snip above, but the true/false is not consistent.

    Below, if a record had a formatted ID or Iteration change, it should be marked as True 
    if a record has the same formatted ID and Iteration change from the previous record, it should be marked as False

    The flag is inconsistent with each record going forward
    The records that are marked red are what I'm trying to mark as changed from the previous record

    Thank you.





    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks very much for helping but your suggestion didn't work out.
      I added a screen print here of what changes I need to track.

      Simply put, if the Formatted ID and /or the iteration changes, that's what i would need to to track.
      I marked each one that would be considered a change, due to iteration and /or formatted ID change.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks very much. I think you nailed it! I'll try it out in a little bit.