Forum Discussion
Use one slicer for two calculated tables
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.
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.
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.
Community SupportHi 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.
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.
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.
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!
- v-xicai
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.