Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Have a look at the LOOKUPVALUE function and potentially RELATED or RELATEDTABLE.
Have a look at the LOOKUPVALUE function and potentially RELATED or RELATEDTABLE.
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?
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |