Need to build a smart sub-table

[jason5703]

This is probably pretty easy to figure out I just have come up with blanks on both how to accomplish and how to search.  I need to create a subtable of distinct id's from a table that contains those id's with multiple characteristics and if one of those characteristics is not matched the subtable will tell me that.  Rudimentary example below.

ID	CHAR_SYS_1	CHAR_SYS_2	SYSTEM MATCH
1000	N	N	MATCHED
1000	BATT	BATT	MATCHED
1000	100	100	MATCHED
1000	10/11/2019	10/11/2019	MATCHED
1001	N	N	MATCHED
1001	BATT	BATT	MATCHED
1001	201	201	MATCHED
1001	09/20/2019	02/03/2018	NOT MATCHED

I would want the subtable to look something like this:

ID	ALL_CHARS_STATUS
1000	MATCHED
1001	NOT MATCHED

[V-lianl-msft · Accepted solution]

Hi @jason5703 ,

Based on your description, create the following measure to get the results you want.

Measure = CALCULATE(MAX('Table'[SYSTEM MATCH]),ALLEXCEPT('Table','Table'[ID]))

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

[HotChilli]

In Power Query, do a 'Group By' on ID and aggregate on the MAX of SYSTEM_MATCH column.

This works because the N of 'NOT MATCHED' is later in the alphabet than M in 'MATCHED'

[jason5703]

I tried the below and the SYSTEM_MATCH column showed every row to be "NOT MATCHED"

New Table =

SUMMARIZE (

ALL (Data[ID],Data[DataSystems]),

Data[ID],

"PER_ID_STATUS", MAX(Data[DataSystems])

)

The Data[DataSystems] column is calculated and not available in PowerQuery

I tried a similar GROUPBY and had the same problem.