cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
systamper Resolver I
Resolver I

Use one slicer for two calculated tables

I have two summary tables that let me calculate a sum of sales by business for two different product lines (Prod a is in one summary table and Prod b is in another summary table). I calculated a rank by sales for each summary table. Here is some sample data:

 

Prod A 

Company             Sales                      Rank

Company 1          $100,000              1

Company 2          $95,000                 2

Company 3          $90,000                 3

 

Prod B

Company             Sales                      Rank

Company 1          $100,000              1

Company 4          $95,000                 2

Company 5          $90,000                 3

 

These relate back to a Company table, and I use this relationship for some cross filtering. The join looks like this:

prod join img 1.jpg

 

I want to create a single slicer that allows me to show the top n from Prod A and Prod B. I was hoping I could create an calculated table with a numeric range of 1 – 50, and then join that back to the Prod A and Prod B tables, like this:

 

prod join img 2.jpg

 

 

However, I can’t link to both Prod A AND Prod B, since it causes the dreaded “Introduce Ambiguity” error.

I also tried creating a calculated column in my two Prod A and Prod B tables that use the max value from Top 50, and I then put a slicer on the page using the Top 50 value. However, I forgot that I can’t force a calculated column to refresh, thereby making this useless.

 

Does anyone have insight into how to make this work using my existing architecture? I could merge the summary tables into a single summary table, and then I would stip away the ambiguity. However, it seems like there has to be a way to use a single slicer to filter Prod A and Prod B at the same time.

Any thoughts or suggestions would be most appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Use one slicer for two calculated tables

Hi @systamper ,

 

You need to show the top 50 of sales for Prod A and top 50 of sales for Prod B separately, right? If yes, you can follow the steps below.

 

1.Enter Query Editor, add the custom column Prod in table Prod A and Prod B.

6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Combine the two tables into single table Append1 to replace table Prod A and Prod B using Append, then the click "Close & Apply" button.

7.png9.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Create rank column in table Append1 like DAX below, let it as the source of Slicer visual.

 

Rank= RANKX(FILTER(Append1 ,Append1 [Prod]=EARLIER(Append1 [Prod])),Append1 [Sales],, Desc, Dense)

 

4.If you need a visual to display the whole top 50 of sales, you can create measure Filter1 table Append1, then put measure Filter1 in the Visual Level Filter of goal visual, and setting the Filter1 as "is not blank".

 

Filter1=IF(MAX(Append1 [Rank])<=50,1,BLANK()))

 

Best Regards,

Amy

 

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

5 REPLIES 5
Community Support
Community Support

Re: Use one slicer for two calculated tables

Hi @systamper ,

 

You need to show the top 50 of sales for Prod A and top 50 of sales for Prod B separately, right? If yes, you can follow the steps below.

 

1.Enter Query Editor, add the custom column Prod in table Prod A and Prod B.

6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Combine the two tables into single table Append1 to replace table Prod A and Prod B using Append, then the click "Close & Apply" button.

7.png9.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Create rank column in table Append1 like DAX below, let it as the source of Slicer visual.

 

Rank= RANKX(FILTER(Append1 ,Append1 [Prod]=EARLIER(Append1 [Prod])),Append1 [Sales],, Desc, Dense)

 

4.If you need a visual to display the whole top 50 of sales, you can create measure Filter1 table Append1, then put measure Filter1 in the Visual Level Filter of goal visual, and setting the Filter1 as "is not blank".

 

Filter1=IF(MAX(Append1 [Rank])<=50,1,BLANK()))

 

Best Regards,

Amy

 

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

systamper Resolver I
Resolver I

Re: Use one slicer for two calculated tables

Thanks for the response, @v-xicai. I actually had a similar solution worked out using Union via a calculated DAX table. I am wondering if it is possible to do so without having to create any additional tables? Doing it in Power Query might have a bit of run time, but for now, I need the flexibility of DAX. But again, I was hoping I could do this without infrastructure changes. Oh well. Thanks!

Community Support
Community Support

Re: Use one slicer for two calculated tables

Hi,

 

Yes, from my solution above, it appended the two tables as a new table beside the Prod A and Prod B. There is another button "Append Queries" which append table2 based on table1, that is to say there is no any additional tables. After that, the table1 contain the whole content of table2, then for the table 2 , you can choose to delete it or not.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support
Community Support

Re: Use one slicer for two calculated tables

Hi  @systamper ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

systamper Resolver I
Resolver I

Re: Use one slicer for two calculated tables

Thanks, Amy.

 

I ultimately solved this using a similar method, but using DAX. I needed to do some additional calculations, so DAX made more sense. But yes, either Power Query or DAX would have worked for this.


Thanks, Scott

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors