Check if one or more rows has a value of true and mark all records as true

Anonymous — Wed, 19 May 2021 00:50:30 GMT

Hi,

I'm trying to put in a measure to work out if a record has been checked.

I have the attached data, with an orderid, and then a duplicates key (some customers can have more than one order delivered by the same driver)

I have a list of order_ids that have been checked externally, and put in a list. If the order_id is in the list, it comes up as IsDuplicate = true.

What I am trying to do is put in the "Has been checked" column (in red). If one of the records with the same key has "isduplicate" = true then all the records with that key should be marked as true.

I am using the following measure:

DuplicatesCheck = CONTAINS(FILTER(Data,Data[DuplicatesKey]=SELECTEDVALUE(Data[DuplicatesKey])),Data[IsDuplicate],TRUE())

but I get the same value as the isDuplicate Column.

I referred to this thread, which got me some of the way there:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Boolean-Aggregate-Functions/td-p/1364240

I'm sure I'm close, but I just can't get there.

order_id	DuplicatesKey	IsDuplicate	Has Been Checked
956687684s	4/1/2021___TRUE	FALSE	FALSE
974646346s	4/1/2021___TRUE	FALSE	FALSE
913576467s	4/1/2021___TRUE	FALSE	FALSE
187704359n	4/1/2021_Driver1_Cust-A_TRUE	FALSE	TRUE
195733809n	4/1/2021_Driver1_Cust-A_TRUE	TRUE	TRUE
115093281q	4/1/2021_Driver2_Cust-B_TRUE	TRUE	TRUE
161218806q	4/1/2021_Driver2_Cust-B_TRUE	FALSE	TRUE
157854642v	4/1/2021_Driver3_Cust-C_TRUE	TRUE	TRUE
161272024v	4/1/2021_Driver3_Cust-C_TRUE	TRUE	TRUE
170455160v	4/1/2021_Driver3_Cust-C_TRUE	FALSE	TRUE
106823034n	4/1/2021_Driver4_Cust-D_TRUE	FALSE	FALSE
143195051n	4/1/2021_Driver4_Cust-D_TRUE	FALSE	FALSE
101293113n	4/1/2021_Driver5_Cust-E_TRUE	FALSE	TRUE
167289758n	4/1/2021_Driver5_Cust-E_TRUE	TRUE	TRUE

Thanks!

Dave

Re: Check if one or more rows has a value of true and mark all records as true

edhans — Wed, 19 May 2021 02:24:25 GMT

Try this Anonymous

Has Been Checked = CALCULATE ( CONTAINS ( Data, Data[IsDuplicate], TRUE () ), ALLEXCEPT ( Data, Data[DuplicatesKey] ) )

I just removed all filters from the table except for the DuplicatesKey. The [Has Been Checked orig] is your text values in the desired goal above.

My measure is the far right column in red.

Re: Check if one or more rows has a value of true and mark all records as true

Anonymous — Wed, 19 May 2021 02:27:11 GMT

Thanks edhans - I had tried something similar and it didn't work - this works perfectly!

Re: Check if one or more rows has a value of true and mark all records as true

edhans — Wed, 19 May 2021 02:33:41 GMT

Excellent. Glad I was able to assist Anonymous

topic Check if one or more rows has a value of true and mark all records as true in DAX Commands and Tips

Check if one or more rows has a value of true and mark all records as true

Re: Check if one or more rows has a value of true and mark all records as true

Re: Check if one or more rows has a value of true and mark all records as true

Re: Check if one or more rows has a value of true and mark all records as true