cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justinsweet Frequent Visitor
Frequent Visitor

One page have different slicers filter different tables

I have a page with 4 filters.  Year, Period, Category and Customer. 

 

When I filter by a specific category, my customer filter results are limited to just the selected category so total categories is 100 and customers is 100,000.  If I select 1 category my customer goes from 100 to 10 (expected).  The first table shows count of customers and sales $   when filtered this is 10, customers $20,000 in sales.  I want a second table that shows for those 10 customers, what are the sales for all of the 100 categories which I would expect to be customer count 10 and sales for those 10 customers of all categories $50,000 dollars since taking in account all 100 categories. 

 

My issue is I can't get just the 10 customers, it does all 100 customers.  Any thoughts why it doesn't keep the customer filter?  If i go and indivisually select each customer then it filters by that, but if I leave blank even though the filter shows 10 customers, it does all 100 customers. 

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

Got it! It was the filter for the Sales not being blank. Seems like that should not be necessary I will have to noodle on that for a while. Since I had a measure already I ended up using this approch rather than using the visual filters (I always forget about them later and waste time figuring out why its not working. 

 

Revenue for All Categories = IF([Revenue],CALCULATE([Revenue],ALLEXCEPT(FACT_SALES,FACT_SALES[CustomerId])))

 

Though I think if I was doing this on my own report I'd use my ALTERNATE as this does not require editing interactions either. 

Revenue for All Categories ALT = VAR Selected_Customers = VALUES(FACT_SALES[CustomerId]) RETURN CALCULATE([Revenue],ALL(DIM_CATEGORY[CategoryName]), FACT_SALES[CustomerId] in Selected_Customers)

View solution in original post

28 REPLIES 28
rajendran Super Contributor
Super Contributor

Re: One page have different slicers filter different tables

Check your relationship and cross filter direction . Make sure the filter direction is from Category to Customer in order to make sure teh filter flows.

 

Thanks

Raj

Highlighted
Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

Its hard to tell exactly what is going on without some representative data, any measures your using etc.  

 

If the masure your using in Table 1 is 

Revenue = SUM(Fact[Sales Amount])

Then the mesure you want to use in table 2 is 

Revenue All Categories = CALCULATE([Revenue],ALL(Fact[Category]))

Note in some situations where you are slicing directly on your FACT table things will not work so I generally create lookup tables that I relate to the FACT table and then slice on those.  Using Category as an example you can use the Create Table button from the modeling tab to create a list of your Categories automatically with this DAX 

Categories = VALUES(Fact[Categories])

You should then set up your slicers on this table (after you relate it to your fact table of course) and then the measure would be 

Revenue All Categories = CALCULATE([Revenue],ALL(Categories[Category]))
justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables

Here are the two images below with what is happening and the desired behavior.  it seems like even though the customer dropdown slicer is filtered to just the 10.  if select all is checked or nothing is checked it does all 48274 customers rather than just the 10 in the dropdown slicer.   Hopefully this makes sense

 

 

 

 

 

WhatHappens.pngWhat is happeningShouldBe.pngShould Be

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables

provided some screen shots below.  maybe this helps

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

Any chance you can share the power bi file?

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables

 


@mnayar wrote:

Any chance you can share the power bi file?


i created the scenario using dummy data, how do I share a .zip file or power bi file on here?

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

Upload it to dropbox or google drive and post the link here.

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables


@justinsweet wrote:

 


@mnayar wrote:

Any chance you can share the power bi file?


i created the scenario using dummy data, how do I share a .zip file or power bi file on here?


https://drive.google.com/drive/folders/1fMCDwFkoVe8cx4ljr8w0fUZyq0ihVNBo?usp=sharing

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@justinsweet link doesnt work

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,211)