By putting Category Type on the sheet, you have directed the sheet to compartmentalize the data by Category Type.
In the attached I added a bogus dimension and then expanded the text box to display what's actually in there.
What are you really trying to do here? The example you gave doesn't say much about what you really aiming for in the larger picture.
For the record, you CAN make your own list of all the dimension values selected on a sheet, and also control what that list looks like. (So, if the user actually has selected all the values, you can set the output string to ALL.) Maybe that's what you are actually aiming for. If so, go to the workbook at this Tableau Public link, and look at tab 15 (last tab at the top in the workbook) to see a method for building that string.
Sample1_v2018.1.twbx 94.3 KB
I am using this in a dashboard as like a header. Depending on the value selected from the filter, the datatable changes. So if I select "Asset", it only say "Asset" as the text view and show data related to Asset and so on. But when the filter is selected as "All" the text view (or header) is showing "Asset Expense....". I want to show this as "All" instead if possible. or may be hide.
I have created a dashboard as an example. I hope that will help to understand what I am looking for. Thank you
Sample1.twbx 104.9 KB
In the attached I added a calc to your sheet2. INDEX tells me how tableau is "walking" along the table for this sheet. I set it to run along [Category Type]. For this simple sheet, TABLE(down) would have sufficed, but your actual sheet may be more complicated, so I set it this way explicitly.
I'll be setting all other table calcs in this exercise the same way.
On Sheet 2(2) I added a new calc. See [Build the list]. This is using the technique in the link I referenced earlier. Notice how the list "grows" as it works down the list.
Sheet (3). My bad. Nothing new there. go to sheet (4). Here I made a final list calc. It grabs the longest of the list and displays in in all rows.
On Sheet 5 I dragged a copy of INDEX to filters. Select only for value = 1. Since the [Final List] value is the same in all cells, I only need to see one of them.
On sheet (6) I removed everything but the [Final List] value from TEXT. And I unchecked "Show Header" on the Category Title pill so that we don't see a category name. Just the text box.
And I also did something else. I created two LOD calcs. One tells me how many Categories are in the database. (FIXED LOD.) And the other tells me how many are actually on the sheet. (EXCLUDE LOD.) Play with the filter on this sheet and see how the displayed list and the two values in the title interact with the filter.
Finally on Sheet 7 I made a copy of the [Final list(copy)] calc. I added logic to override the value if the two counters are equal. Again, play with the filter there.
One last note. If your filter is actually going to be set up as single-select (or ALL) as you have on your dashboard, much of those steps will not be necessary. You don't need to build the list and derive a final list. the [categories on sheet] calc will either be 1 or 5 in that circumstance. If the two counts are the same, then ALL, else ATTR([Category Title]). You'll still need INDEX though, to limit the number of rows on the sheet to the first row when ALL is selected. If it turns out this is all you need, squirrel away this workbook so that the day you actually need to build a list of the selected dimension values, you'll have a working example to follow.
Sample1_v2018.1 A.twbx 132.6 KB
Hi Joe, Thank you so much for the detail explanation. It really helped me. Sheet 7 is what I wanted.
Thank you again for the quick help.