Forum Discussion
Helper INeed to build a smart sub-table
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 |
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.
3 Replies
- HotChilli
Community Champion
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'
jason5703I 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 PowerQueryI tried a similar GROUPBY and had the same problem.
V-lianl-msftCommunity Support
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.
