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

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
justinsweet
Regular Visitor

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

 

 

 

 

 

What is happeningWhat is happeningShould BeShould Be

Anonymous
Not applicable

Any chance you can share the power bi file?

 


@Anonymous 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?


@justinsweet wrote:

 


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

Anonymous
Not applicable

@justinsweet This seems expected behavior.

 

I will try to explain this better. So when you select the category slicer the customer slicer get filtered (expected) and nothing from sales table gets filtered as the interaction is removed(expected). so by default it will show all data for the selected time period.

 

you only see 2 customers 12 and 13 in there and none are selected so no interaction is happening by default it is using all customers, since nothing is selected hence it is unfiltered state.

 

If you are looking for total sales of customers filtered like 12 & 13. Renable the interaction with product and calculate the sales field so it is not impacted by product i.e. using all except. If you want other columns you can include those as well.

 

ALL EXCEPT USAGE

 

SumSales = CALCULATE(SUM(FACT_SALES[Sale]), ALLEXCEPT(DIM_CUSTOMER,DIM_CUSTOMER[CustomerId]))

yeah maybe 


@Anonymous wrote:

@justinsweet This seems expected behavior.

 

I will try to explain this better. So when you select the category slicer the customer slicer get filtered (expected) and nothing from sales table gets filtered as the interaction is removed(expected). so by default it will show all data for the selected time period.

 

you only see 2 customers 12 and 13 in there and none are selected so no interaction is happening by default it is using all customers, since nothing is selected hence it is unfiltered state.

 

If you are looking for total sales of customers filtered like 12 & 13. Renable the interaction with product and calculate the sales field so it is not impacted by product i.e. using all except. If you want other columns you can include those as well.

 

ALL EXCEPT USAGE

 

 

SumSales = CALCULATE(SUM(FACT_SALES[Sale]), ALLEXCEPT(DIM_CUSTOMER,DIM_CUSTOMER[CustomerId]))


Yeah, maybe you can help a little explain.  this I am not sure still helps the desire behavior cause it will be filtering the number still on the selected category.  what I want in the at 2nd grid is the all sales for all categories (expect the one selected) but only for the 2 customers 12/13.  Does that make sense.  its really to analyize these customers that bought this product vs what else do these 2 customers by 

Anonymous
Not applicable

create a table with 2 columns

 

1st col is just sum of sales based on filter

2nd cold is sum of sales using the formula i told you. this folmula will calculate all sales for those filtered people by the product filter.

 

Partial vs All Sales.PNG


@Anonymous wrote:

create a table with 2 columns

 

1st col is just sum of sales based on filter

2nd cold is sum of sales using the formula i told you. this folmula will calculate all sales for those filtered people by the product filter.

 

Partial vs All Sales.PNG


 

thanks, getting closer I think but 1 issue I am still having.  This is doing the sum of all products but it still doesn't solve the customer issue.  the exmaple above works cause the filter has all the customers. lets say in the example you provided customer 12 didn't have a sale for Product 1 and only had sales for product 2, only customers 1 and 13.  i would want the grid to show what it does now, just w/out customer 12. i only want to see the total sales for customers 1 and 13.  

 

 

example1.png

Anonymous
Not applicable

@justinsweet actually this works for that as well, here is a screenshot. 

 

Challenge -Partial vs full sales.PNG


@Anonymous wrote:

@justinsweet actually this works for that as well, here is a screenshot. 

 

Challenge -Partial vs full sales.PNG


 

hmm.  well the screen shot appears to look good.  I can not get the same behavior.  any chance you can take a quick look at the report i pushed and see if you can do the same calculated measure and send back to me?  

Anonymous
Not applicable

@justinsweet 

 

Check this out. I enabled the interaction for the table u dont want filtered. and added sumsales column

 

SumSales = CALCULATE(SUM(FACT_SALES[Sale]), ALLEXCEPT(FACT_SALES,FACT_SALES[CustomerId],FACT_SALES[Date]))

 

Also add a filter for the 2nd table where sale is not blank.

 

 

Capture.PNG

@Anonymous yes - the link was on my reply to Justin but here it is again- https://1drv.ms/u/s!AuCIkLeqFmlhhJocyRDRZ8OZBjsGkA

Anonymous
Not applicable

just renable the interaction and add the total sales using allexcept and all will work for you. I added a screenshot above.

@Anonymous wont seem to work for me whatever I do. I know I'm probably doing something stupid and can't see the forest for the trees.  Would you mind looking at the file I uploaded and checking it out?

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)

thanks @Seward12533 and @Anonymous    I wasn't able to get mnayar solution to work locally but the ALT metric seward seems to have did the trick!

To get @Anonymous's solution to work you needed to disble the interactions between the left matrix and the custoemr slicer and filter the visual only show when revenue is NOT BLANK.

 

The interactions are controlled from the "Format" tab when the visual is selected and the matrix visual filters from the filter pane.  Screenshots below

 

snip_20180810155706.pngsnip_20180810155543.png

 

@Anonymous @Anonymous the screenshot does not appear to solve the problem as the resultes show mulitple cusotmers and products and I could not get the allexcept to work either.

 

@justinsweet try this solution

 

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)

 

  • Selected Customers gets list of customers included in the fitlers
  • the ALL statement removes the filters for Category
  • FACT_SALES[CustomerID] in Selected_Customers filters for selected customers.

Here is a link to PowerBI - one thing that is odd is I could not figure out what how your second table was showing all customers. How did you remove the filter context on that one?

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJocyRDRZ8OZBjsGkA

 

capture20180810024228573.png 

Anonymous
Not applicable

@Seward12533 Hello, in the screenshot, left side is all unfiltered data and right side is the result. The filtering is done by the Product Category Filter and the ALL Except is used to calculate the sum of sales for customers no matter what filter is applied.

@Anonymous I keep forgetting about ALLEXCEPT so thanks but can you please look at the workbook link I attached and explain why it is not working correctly for me?  I was able to get it work in the end using the hack I described but woudl be easier to just use ALLEXCEPT except I can't seeme to get it work as I expected.

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.