4 Replies Latest reply on Apr 5, 2018 10:21 AM by michaels.kale

Level of Detail filtering issues

All,

I've had an issue with LOD expressions for a while when I attempt to integrate filtering and I'm wondering if anyone has hit the same problem and knows a way around it - or if I'm simply approaching the problem incorrectly.

I attached a small sample workbook. The issue comes about when I try to incorporate a total count with Country / State filtering.

Count = sum(Count)

Total Count All = {FIXED [Country], State, [Type] : sum([Count])} (i.e. this is my attempt to get the sum(count) of the particular country, state, and type shown or filtered for)

% of Total = sum([Count]) / sum({FIXED [Country], State, [Type] : sum([Count])})

The initial state of the dashboard is correct. However - if I filter for Exempt, you'll see that PC shows a count of 60, 60, 100%.

In reality - PC Exempt should show 60 (exempt), 110 (total), and 54% of total.

The reason it is not showing the extra 50 (USA, CA, PC, NonExempt) - is because there does not exist an "Exempt" status for USA,CA,PC. I thought this wouldn't matter because I'm using to LOD expression to ignore the fact that I'm filtering for Status (at least my intention).

In fact - If I were to add a line that is USA, CA, PC, Exempt - with a NULL or 0, Tableau will correctly pick up the NonExempt 50 and show 110:

To me this is a bit baffling as it means that the problem isn't that I've filtered for Exempt (because it still grabs NonExempt in the last example), but the problem is that there does not exist a combination of Exempt/NonExempt for every single Country/State/Type.

In fact the only way I've ever found to fix this problem is to create a Cartesian join/cross join to insert a bunch of fake data with NULL's. But this isn't really practical and I can't do that in large datasets where I may be duplicating tens of thousands of rows.

Am I going about this the wrong way - or is there a solution I haven't thought of to get around this problem?

• 1. Re: Level of Detail filtering issues

Hi, Michaels

The reason is in your LOD, you used country, state and then type. if the total is irrelevant to country and state, you can get rid of them.

then you can use it for the % calculation as well.

Hope this helps

ZZ

• 2. Re: Level of Detail filtering issues

Unfortunately Country/State is needed here. If I were to get rid of it and filter for the US - the total would be worldwide. Which may be relevant in some situations, but I would like to here if possible.

• 3. Re: Level of Detail filtering issues

Michaels,

You can have the Country and State filters effect the total.  Set it up like Zhouyi Zhang has it.  Then add Country and State to context:

• 4. Re: Level of Detail filtering issues

Thanks - I think this does it. I had tried adding to context in the past but I didn't think to remove it from the LOD, so I'm not sure it ever worked for me. Appreciate it!