Both tables are associated by the column letter. When I don't select data by Date I manage to get all the values of letter in table One, but once I select a date interval I only get values A, C and D for letter (as is in table two). How can I keep obtaining all the values of letter in table one? Even if their count in table two is zero?
It seems pretty obvious to me why it isn't working but I just can't figure out how to make it work or if it is even possible to make it work.
I think you have at least a couple of options, but it all depends what is the final requirement for your app, so it's very hard from my perspective to find the right solution for you. Nevertheless - good luck, hope the below will help at least any how.
1. Currently your two tables are associated by column 'letter'. If you will rename 'letter' to something else in one of the tables you will break the link between two tables and any selections made in 'Date' will not affect values in Table One.
2. You can concatenate Table Three to your Table Two, where Table Three has the same structure as Table Two with "missing" letters initiated and Equip. Number and Date columns set as 'N/A' or something else. In that situation tables Table One & Table Two (Table Three is concatenated to Table Two, so it doesn't exist as a separate object) are still associated or:
3. you can complete the same steps as point 2 but you can try left join/outer join Tables One and Two, it's up to you.
Not sure if this will help you... maybe give us some extra information on the requirements and how Table One and Table Two are associated with the rest of your data model.
What I need is that the letters in Table One aren't ignored if they don't appear in table two.
On my actual Table One, I have a list of equipments, and table two is made of the list of malfunctions for those equipments. However some equipments haven't yet malfunctioned and therefore do not appear on table one or on the table or are counted on a bar chart I build for visualization. This is because everytime I apply a filter only the equipments that have malfunctioned are selected since table one has no date information and the date selection is made only on table two. Nonetheless I need the equipments that have not malfunctioned to have a count of 0 occourrences in table two.
I have plenty more of variables but they are:
Measure: If (IsNull(Aggr(Count(Ordem) OR 0, [Equipamento-Num. SAP])), 0, Aggr(Count(Ordem), [Equipamento-Num. SAP]))