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

 

Highlighted
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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors