Hi, the way you have it set up currently your lod calc will count distinct product names for each customer over all years (and all other fields).
Fixed calculations happen before your quick filters are applied. If you right click your (Year(Order Date) filter and select "add to context" then that year filter will be applied before your lod calc.
So regular dimension filters are applied after an FIXED LoD is calculated, so filtering on date has no affect on the assignment of people to bins (as it's an FIXED LoDs)
So to get round this, we can 'bump' the filter up the calculation pipeline, by making it a 'context filter'...click on the filter (Order Date) and select 'Add to context' (it will turn grey to let you know it's done)...it will now affect FIXED LoDs (there can be a performance cost to this, but unless your data set it huge/complicated you'll barley notice)
Hope that helps.
Hi Simon and Chris thanks for the replay,
my data set is huge can I apply context filter? also can I apply multiple context filters?
Is there any other method to achieve this?
I would try using context filters and see if it hurts your performance too much. There would be slightly altered approaches using "include" instead of "fixed", but i cant speak to the efficiency of one vs another.
Data source am using is MySQL and my Dashboards has too many filters and sheets, performance is poor as am using live connection,
Do context filters add additional burden to the performance?
As Chris says, it's a bit of a "try it and see" in terms of performance with FIXED LoDs and context filters. There are times (especially when the data is large, and the filter is filtering out a 'vast majority') that a context filter can actually speed things up (I've deliberately used the vague term 'vast' as it depends on the data structure...etc. but 85%+ is the kind of level where you 'can' see improvements). Context filters...kind of (there is a bit more to it!..but in short) build a temp-table (of the filtered data) that the queries are run against, so if the time reduced by running queries over less data, out-weigh the time it takes to build the table you get a performance increase (a bit like datasource filters)...this is all 'in theory'
“In theory, theory and practice are the same. In practice, they are not.” Einstein
...I've found no hard and fast rule, or heard of one!
Include/Exclude are generally more efficient than FIXED LoDs...FIXED LoDs (generally) create temp-tables of the level required and join it back on to the main data (at that level), whereas Include/Exclude run 'locally' (and are also responsive to regular filters)...so seem (IMHO) more akin to Table Calculations in their speed.
I say, give it a go (use the performance recorder if required...there could be other calcs you could improve to 'allow' for the FIXED LoD cost), and if it is really bad, try creating the same calc with include/exclude and non-context filters...and let us know how you get on!
...are you not able to use an Extract?...this will be so much faster than MySQL. Lots of drop-down filters (especially if they are all 'Relevant Values Only'...or now Context) will slow things down, regardless of what else is going on (...every-time one is selected, Tableau has to query the data to get the LoV for the other filters). I tend to build my dashboards, with dashboard actions as the primary way of playing/filtering the data to keep the number of drop-down filters to a minimum.
As the data updates daily I am forced to use live connection and paying heavy price for it, will try the mentioned methods by you and let you know,
Thank for the help guys
Best of luck!...
Although some of this is directed for TDEs...there are some other, general, advise on things to try to speed things up
this one focuses more on design...but still got some things to try