cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Filter functions

Hi All,

 

I try to filter a table by year , using DAX.  My calculation is like this but it does not work.. Can you help?

 

Measures = Filter(Sales,Sales[Year]=2016)

 

What I have is...

 

Table : Sales

Date             Branch            Sales           Items               Year

1 Jan 16        Tokyo             100             Chocolate       2016

3 Jan 16        Nagoya           50              Candy             2016

9 Feb 16       Tokyo              30              Biscuits           2016

2 May 17      Tokyo              20              Gum               2017

24 Dec 17       Osaka              40            Biscuits           2017

5 Jan 18        Nagoya           20              Chocolate       2018

1 Apr 18       Tokyo              30              Candy             2018

 

What I want to is... sales by year

 

Table : 2016

Date             Branch            Sales           Items               Year

1 Jan 16        Tokyo             100             Chocolate       2016

3 Jan 16        Nagoya           50              Candy             2016

9 Feb 16       Tokyo              30              Biscuits           2016

 

Table: 2017

Date             Branch            Sales           Items               Year

2 May 17      Tokyo              20              Gum               2017

24 Dec 17       Osaka              40            Biscuits           2017

 

Table : 2018

Date             Branch            Sales           Items               Year

5 Jan 18        Nagoya           20              Chocolate       2018

1 Apr 18       Tokyo              30              Candy             2018

 

I initially tried it by using filter in query function but it causes DataFormat.Error:External table is not in the expected format ...

So I can not refresh data any more.

 

I am thinking if I can do it by using DAX. Appreciate your help.

 

All the best,

cocomy

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filter functions

Hi cocomy,

 

To achieve your requirement, you can create three calculate tables by clicking Modeling->New Table, then use DAX formula:

Table_2016 = CALCULATETABLE(Sales, Sales[Year] = 2016)

Table_2017 = CALCULATETABLE(Sales, Sales[Year] = 2017)

Table_2018 = CALCULATETABLE(Sales, Sales[Year] = 2018)

2.PNG

1.PNG

 

 

Regards,

Jimmy Tao

View solution in original post

7 REPLIES 7
Highlighted
Resolver I
Resolver I

Refresh : External table is not in the expected format

Hi All,

 

When I duplicate a table in query, it shows DataFormat.Error:external table is not in the expected format and can not refresh.

 

I have a table of sales record and want to create different tables by filtering by year.

 

(1) Sales table

Date               Branch                    Sales

2 Jan 16          Tokyo                    50

5 May 16        Nagoya                 300

1 Jan 17          Tokyo                    200

5 Jan 17          Osaka                    100

.

.

1 Dec 18          Nagoya                  50

 

(2) I want to divide into 3 tables by filtering table(1) by year

 

2016 Sales

2017 Sales

2018 Sales

 

Is there anyway I can do without causing DataFormt.Error?

 

All the best,

cocomy

Highlighted
Super User IV
Super User IV

Re: Filter functions

Hi,

 

Build a table visual and in the visual filter section, drag the Year.  Select any year in the Filter section.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Resolver I
Resolver I

Re: Filter functions

Hi

 

Thank you for your suggestion. I am going to make a graph by year like this. (see attached)

If I filter by vizualization, I can only get one year by one year so I can not compare them at once?

 

All the best

cocomy

Untitled.png

 

Super User IV
Super User IV

Re: Filter functions

Hi,

 

This should get you started.  Download the file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Resolver I
Resolver I

Re: Filter functions

Hi Ashish

 

Thank you very much  for your advice.

 

Apology, my explnanation was not clear.  X is Month and Y is Sales and I would like to compare Year on Year.

 

 

All the best,

cocomy

Highlighted
Community Support
Community Support

Re: Filter functions

Hi cocomy,

 

To achieve your requirement, you can create three calculate tables by clicking Modeling->New Table, then use DAX formula:

Table_2016 = CALCULATETABLE(Sales, Sales[Year] = 2016)

Table_2017 = CALCULATETABLE(Sales, Sales[Year] = 2017)

Table_2018 = CALCULATETABLE(Sales, Sales[Year] = 2018)

2.PNG

1.PNG

 

 

Regards,

Jimmy Tao

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Filter functions

Hi Jimmy

 

Thank you very much for your help. This is what I have been looking for.

 

All the best,

cocomy

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors