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?

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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors