Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gtamir
Post Patron
Post Patron

Page Filtering on visualisation and Slicer

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

1 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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-danhe-msft
Employee
Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

ScreenHunter_018.jpg

Hi @gtamir,

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

Sample data

Measure=

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

1.PNG

After selected the "August":

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

ScreenHunter_007.jpg

 

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Giora

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.