Page Filtering on visualisation and Slicer

[gtamir]

Hi

I am trying to understand the princip of filtering on a page.

I have "sales" Table for the whole with "shop code" column and other fields.

I have "Customers" Table with "Shop Code" as well. It is the shop he was registered at.

I create visualisation with "Shop Code" column from "sales" and next to it some calculations and other fields.

I create a slicer and filter the table to one specific month.

Now, I want to add a column from "customers" that count all customers for each shop. But this column is filtered too.

How Can I add this column without beeing filterd?

Thanks Giora

[v-danhe-msft]

Hi @gtamir

I could not figure out the sentence "this column is filtered". Is this column a calculated column or a measure? Could you please post me some sample picture and your disired result or share the pbix file if possible?

Regards,

Daniel He

[gtamir]

All the fields are filtered by the slicer for June.
But I want the last column (Count of CustomerKey) not to be filtered. I want the total number of  customers registered in a specific Pharmacy. In the file "Customers 6-2018" table there is a field "Pharmacy code". This is the pharmacy the the customer was registered.

[v-danhe-msft]

Hi @gtamir,

Based on my test, you could use the "All" function:

Sample data

Measure=

Measure = CALCULATE(SUM(financials[ Sales]),ALL('financials'[Date].[Month]))

After selected the "August":

You can also download the PBIX file to have a view.

 https://www.dropbox.com/s/mt56fw7x1wbybu6/Page%20Filtering%20on%20%20visualisation%20and%20Slicer.pb...

Regards,

Daniel He

[gtamir]

Thank you Daniel for your answer.

The column I try to put in the report is different table then the rest of the columns.

All the columns are from "Sales" and this one from "Customers 6-2018".

I already tryied the all function but it didn't work.

I want all the customers filtered by "Pharmacy Code" without filtering.

For example 1014 should be 770.

Could you look to the image I attach. On the top is the formula and on the right are the tables.

Thanks Giora

[v-danhe-msft]

Hi @gtamir,

I think the measure you have created shoud be modified, the column after the ALL function should be the column that you are using as the slicer, like the picture I post before is the [Date].[Month], I suggest you test again and If issue persists, would you please share your pbix file if possible? 

Regards,

Daniel He

[gtamir]

https://drive.google.com/file/d/120sao6wnH21sZf5GXHSIpFqkKPi98Tjx/view?usp=sharing

I summarize my problem:

In the Tab: "Total Count Of Costomers" is the number of all customers.

In the tab "June 2018" I want the column Measure will not be filteres by the slicer. 

Thanks Giora

[v-danhe-msft · Accepted solution]

Hi @gtamir,

I have modified your formula like this:

Measure = calculate( COUNT('Customers 6-2018'[CustomerKey]), ALL('DimDate'[MonthofYear]))

Hope it is what you want.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/8j954c3x7jx0j1d/Page%20Filtering%20on%20visualisation%20and%20Slicer2.pbix...

Regards,

Daniel He

[gtamir]

Yes it works. So I have to remove the filter from the month and not from the customers counting.

Giora