Filter functions

[cocomy]

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

[v-yuta-msft · Accepted solution]

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)

Regards,

Jimmy Tao

[cocomy]

Hi Jimmy

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

All the best,

cocomy

[Ashish_Mathur]

Hi,

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

[cocomy]

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

[Ashish_Mathur]

Hi,

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

Hope this helps.

[cocomy]

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

[cocomy]

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