cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## New table applying some filters

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.

1 ACCEPTED SOLUTION
Solution Sage

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] ) )
)

Proud to be a Datanaut!

2 REPLIES 2
Solution Sage

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] ) )
)

Proud to be a Datanaut!

Regular Visitor

Thank you! Amazing!  it worked like a charm!