Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Muki
Helper I
Helper I

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

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

 

groupBy.jpg

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.