cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

@mnayar 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.

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@Seward12533 I am waiting for IT to update my Power BI to the latest version so I can access your file. But I think I know the issue.

 

You have edited interactions to not allow it to be filtered by Category right????

 

Solution

1) First Column SUM(Sales) : This will give sales for the filtered customer for the specific category

2) Second Column: Sum(Sales) using AllExcept Formula by customer id: this will gave sales for the filtered customer no matter what category.

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

@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

Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

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

mnayar Established Member
Established Member

Re: One page have different slicers filter different tables

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

Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

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

Highlighted
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

justinsweet Frequent Visitor
Frequent Visitor

Re: One page have different slicers filter different tables

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

Seward12533 New Contributor
New Contributor

Re: One page have different slicers filter different tables

To get @mnayar'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

 

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 (4,876)