New table applying some filters

[brunofs]

Hello there.

I’ve searched but could not find na answer...

So, I have table like this:

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

(…)

I need a second table (preferable in DAX) with these premises:

• E01A must be <> BLANK()  ( it must be an valid number, from 1 to 100)
• YEAR RANGE = 2013 to 2017
• Only 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.

The others parameters (e01C and E01D) are not supposed to appear.

I’ve tried every formula I know, from calculatetable, to filter with addcolumns

Thank you

Cheers from Brazil.

[LivioLanzo · Accepted solution]

could you give this a go and let me know? @brunofs

Table =
FILTER (
    SELECTCOLUMNS ( Data, "ID", [ID], "Year", [YEAR], "E01A", [E01A] ),
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( Data, Data[ID], Data[YEAR] ),
            ALLEXCEPT ( Data, Data[ID] ),
            Data[YEAR] >= 2013,
            Data[YEAR] <= 2017,
            NOT ( ISBLANK ( Data[E01A] ) )
        )
    )
        = 5
        && [Year] >= 2013
        && [YEAR] <= 2017
        && NOT ( ISBLANK ( [E01A] ) )
)

[brunofs]

Thank you! Amazing!  it worked like a charm!