New table applying some filters

Question

Hello there.I’ve searched but could not find na answer...&nbsp;So, I have table like this:&nbsp;IDYEARE01AE01CE01DABAN028002017605,260ABRA02950201364&nbsp;&nbsp;ABRA02950201165&nbsp;&nbsp;ABRA02950200772&nbsp;&nbsp;ABRA02950200866&nbsp;47ABRA02950201562&nbsp;53ABRA02950201665&nbsp;50ABRA02950201467&nbsp;&nbsp;ABRA02950201761&nbsp;54ABRA02950201067&nbsp;52ABRA02950200968&nbsp;49ABRA02950201262&nbsp;&nbsp;ACLA005002015822,650(…)I need a second table (preferable in DAX) with these premises:E01A must be &lt;&gt; BLANK()&nbsp; ( it must be an valid number, from 1 to 100)YEAR RANGE = 2013 to 2017Only the ID which has valid numbers from the columns “E01A”, in every year (2013, AND, 2014, AND, 2015, AND 2016, and 2016) supposed to show.If you notice, the first ID “ABAN02800”, only have records for 2017, so it is not supposed to show. In opposite, ID “ABRA02950” is ok for that criteria.&nbsp;The others parameters (e01C and E01D) are not supposed to appear.&nbsp;I’ve tried every formula I know, from calculatetable, to filter with addcolumns&nbsp;Thank you&nbsp;Cheers from Brazil.&nbsp;&nbsp;&nbsp;

liviolanzo · Accepted Answer

could you give this a go and let me know?&nbsp;brunofs&nbsp;Table&nbsp;=FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SELECTCOLUMNS&nbsp;(&nbsp;Data,&nbsp;"ID",&nbsp;[ID],&nbsp;"Year",&nbsp;[YEAR],&nbsp;"E01A",&nbsp;[E01A]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATETABLE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE&nbsp;(&nbsp;Data,&nbsp;Data[ID],&nbsp;Data[YEAR]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;Data,&nbsp;Data[ID]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[YEAR]&nbsp;&gt;=&nbsp;2013,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[YEAR]&nbsp;&lt;=&nbsp;2017,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;Data[E01A]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[Year]&nbsp;&gt;=&nbsp;2013&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[YEAR]&nbsp;&lt;=&nbsp;2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;NOT&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;[E01A]&nbsp;)&nbsp;))

Forum Discussion

New table applying some filters

2 Replies

Related content

Filtered table

Apply data to all rows in a filtered column

How to apply Slicer filter on un-matching record between two different table.

Can I filter the 1st fact table which then filters a 2nd fact table?

Apply Filter before Calculating Average

ID	YEAR	E01A	E01C	E01D
ABAN02800	2017	60	5,2	60
ABRA02950	2013	64
ABRA02950	2011	65
ABRA02950	2007	72
ABRA02950	2008	66		47
ABRA02950	2015	62		53
ABRA02950	2016	65		50
ABRA02950	2014	67
ABRA02950	2017	61		54
ABRA02950	2010	67		52
ABRA02950	2009	68		49
ABRA02950	2012	62
ACLA00500	2015	82	2,6	50