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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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.  

StevenT_0-1671482244683.png

Thanks, Steven

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1671614704007.png

 

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1671614704007.png

 

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
Not applicable

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

StevenT_1-1671539709035.png

Thank you.





Anonymous
Not applicable

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.

StevenT_0-1671644907460.png

 

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Here are the steps you can follow:

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

vyangliumsft_0-1671503567891.png

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:

vyangliumsft_1-1671503567893.png

 

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
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.