is right friend, you have too many filters and that can affect your pierce mance, good practices say that at most 4 or 5 filters friend, I recommend you combine them with action filters
I know you have sensitive info, but it's going to take hands-on to show you what you need to do.
Check out the video here:
Just hack up something that simulates what you have. Make bogus data in your excel file. I'll only need a few rows of data: one with ALL shortages, one with SOME shortages, and some with NO shortages. And I'd like enough rows to have at least one model with an item that has two ORGs. I just want to make sure I satisfy all the conditions you'll encounter to make sure the solution will work.
In short, I'm going to make a table calc that will look at the full set of [Value] numbers within an org (within an item, within a model), and if any are negative, then grab the whole row from the sheet.
Actually, I think we're good. After watching your video, I was going to extract a chunk and blur some lines, but I don't think this particular report has anything. The orgs are stated in number, so the location isn't apparent, the item numbers are whatever, and the descriptions are (much to my chagrin) not very informative. We should be good. I'm attaching an extract to the original post.
In your original sheet I created two calcs. See [Shortage]. If you add it to the TEXT shelf, you will see either a 1 or 0 below each number. If the sum of this calc across the line is > 0 then at least one cell has a negative number. I do that operation in [Get shortage lines]. For this table calc, the default of TABLE(across) is the right setting. Under different configurations of dimensions on the sheet, you might have to specify the order of dimensions to evaluate across ([Model], [Item], [Org]), and maybe you'd have to tell to restart every [Org]. But for this sheet, Table(across) is perfect.
So I added [Get Shortage line] to the sheet, made it DISCRETE, and shoved it after the [Org] pill on ROWS. You can see that for any row with any red, the value is 1. Otherwise it's NULL.
On Pipeline(2) I moved it from ROWS to FILTERS, and selected value = 1. Now only those rows where the value is 1 gets displayed, and the whole row displays.
Shortage Report Current_v10.4.twbx 313.4 KB
Oh man! That's EXACTLY what I needed. And it makes so much sense to do the calculation here instead of in the raw data.
That said, Can you explain to me why it wasn't working before using the raw data?
I'm not sure what [Shortage] is based on. I assume it's a behind-the-scenes computation in the creation of the data source that looks at the SUM([Value]) and gets set to "Shortage" or "No Shortage". If that's true, it's done at the equivalent of the cell level that we see on the sheet, and therefore doing a quick filter on it will exclude rows that constitute individual cells, not whole rows of Model/item/org.
And yes, in fact, I changed the sheet to use [Shortage] as the COLOR differentiator, and individual cells are colored the same as they were when SUM([Value]) was on the color shelf.
Now that I see this, I actually could have used LODs to mark the whole row as having a shortage or not. The one thing about LODs is that when you have filters on the sheet, you have to make sure all the filtered dimensions are added into the include/exclude, and with that many filters I would find it tedious to get the right LOD. Other people are absolute wizards with LODs, and they might find it a breeze. I'm more old-school, and table calcs are rather natural for me.
But anyway, the point is that you want to look at the whole row, not just the individual cells in the data grid, and [Shortage] is tied to the individual numbers in the grid, and that's why it was throwing out individual cells.