Filter table based on two columns and add non calculated columns to it

[Muki]

Hi

I have a source Table A which contains values from appended/ merged tables.

I would like to structure the data inside this source table so I get unique records which I need for another merge...

In another topic I learned how to create a new table based on a filter on  2 columns which you can find below.

But what I'm still struggling with is the following:

I group by productversion/productname and then create calculated columns in table B like count of records etc. 

I would like to extend TableB with values from other columns : tableA. jv, tableA.base, ... (+ other columns which are not in this example TableA) based on column tableA.reqdate. For instance, I take the max date in column tableA.reqdate and retrieve the corresponding value in column tableA.jv. 

TableB =

ADDCOLUMNS(
SUMMARIZE(
TableA;
TableA[productversion];
TableA[prodname];
"earliestmnrdate";CALCULATE(min(TableA[mnrreqdate]));
"mnrcount"; CALCULATE(DISTINCTCOUNT(TableA[mnrid]))
);
"sourcecount"; CALCULATE(distinctcount(TableA[sourceid]));
"earliestsourcedate"; CALCULATE(min(TableA[sourcereq]))
)

Thanks in advance for having a look !

Muki

[Greg_Deckler · Accepted solution]

Have a look at the LOOKUPVALUE function and potentially RELATED or RELATEDTABLE.

[Muki]

thanks @Greg_Deckler the lookupvalue function does indeed what I'm looking for.

But I have still one error message for the search_column in which I have empty dates :

A table of multiple values was supplied where a single value was expected.

How can I ignore the empty dates in the column?

[Greg_Deckler]

Use VAR to create a temp table with your formula and then pass it through a FILTER(tmpTable,NOT(ISBLANK([Column]))), something along those lines.