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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors