Skip to main content
cancel
Showing results for 
Search instead 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

Reply
brunofs
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
LivioLanzo
Solution Sage
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] ) )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
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] ) )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thank you! Amazing!  it worked like a charm!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors