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
Anonymous
Not applicable

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
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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
v-xicai
Community Support
Community Support

Hi  @Anonymous ,

 

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

Anonymous
Not applicable

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

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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.

 

Anonymous
Not applicable

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!

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.

 

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.