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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
boykin188
Frequent Visitor

Multiple rows per unique ID

I have a table with multiple rows per unique ID, and some columns which reference "flags" (basically a 1 or  0) that something has occurred relative to that ID. Due to the way the data is structured, there can bemultiple instances of a flag showing up for each ID:

ID      ColA ColB ColC
31      1       1        0
31      0      1        0
31      1       1       0
144     1      0       0
144     1     0       0
144     1     0       0
26      0      0       0
26      0      0       1


But I don't care if the "1" shows up multiple times for each ID --- I want to add three columns which basically indicates, for each ID, whether or not a flag has occurred there ever (as indicated by the "1"). Call them ColA_Flag, ColB_Flag, and ColC_Flag. 


So for ID 31, there would be a "yes" for Col A amd Col B, but a "no" for Col C. 

 

So it would look like this:

 

ID      ColA ColB ColC   FlagA   FlagB  FlagC
31      1       1        0         Yes     Yes       No
31      0      1        0           Yes     Yes      No
31      1       1       0          Yes      Yes     No
144     1      0       0          Yes      No      No
144     1     0       0           Yes      No      No
144     1     0       0           Yes      No      No
26      0      0       0          No      No       Yes
26      0      0       1          No      No       Yes


Any help is appreciated!

1 ACCEPTED SOLUTION
synergised
Resolver II
Resolver II

You can use Min / Max

 

FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")
 
 
minmaxtable.png

View solution in original post

2 REPLIES 2
synergised
Resolver II
Resolver II

You can use Min / Max

 

FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")
 
 
minmaxtable.png

Thanks! Worked great!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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