Trouble with single date selection excluding monthly grain data

[mosherterry]

I currently have a data model with two related fact tables, one with a daily grain and one with a monthly grain.  I need to be able to make a selection on a single date without excluding any of the data from the table with the monthly grain.

I've read through the following posts which seem very relevant to solving this problem however I just can't seem to get this right after trying my hand at the various techniques illustrated in each:

• Fact Table with Mixed Granularity
• Generic Keys 

I've tried creating several different versions of a bridge table using composite keys (like concatenating [Facility Code] & '|' & [Client ID] AS [_Facility Patient Key] as well as generic key placeholders like '<ANY>' but no matter what I've done it's still excluding people from the monthly grain table when I have a single date selected from the daily grain table.

To put some business context around this: we have certain revenues that are calculated and reported at a daily grain but a persons monthly rent is recorded at a monthly grain.  What's currently happening is that when I select a single date (e.g. 7/31/20221), it's excluding anybody from the monthly rent revenue table who wasn't here anymore at that point in the month (for instance a person who left the facility on 7/14/2021).  I need to be able to include the full months rent revenue, including people who left prior to the selected date.

Thanks in advance for any assistance!

[anthonyj]

Hi @mosherterry ,

Can I confirm that you don't want any interaction to occur with the monthly grain at all when you select a particular Census Date. Because if you don't want the visualisation to change you could put it in a different state. This would prevent any selections from the default state affecting your monthly data.

Regards

Anthony

[marcus_sommer]

Usually creates multiple fact-tables and the attempt to associate them much more efforts and trouble as following the general recommendation of merging the data within a single fact-table. Often and I think also in your scenario the tables could with only less adjustments simply be concatenated.

Therefore I suggest to go this way because both tables share most of the fields respectively could be probably rather easy adjusted. I assume that you struggles comes here with the date-field on one side and a period on the other side - but they could be harmonized, for example with something like makedate(year, month, 1) to set each monthly value to the first calendar-day of the month. The most views in the UI will work fine with it without further measurements and are there really requirements to mix up daily and monthly views you could adjust the expressions accordingly (with an additionally source-field within the table the different areas could be easily differentiated).

Beside simplifying the efforts to develop a workable and readable datamodel without troubles to associate multiple fact-tables it avoids also the problem of missing key-values on any or even more likely on all sides. It's for example quite usual that new/old products aren't always on both sides or by similar logics of comparing sales and budget that there are the right data everywhere. Such missing keys will always lead to any gaps. Of course missing keys might be identified and appropriate populated or be fetched with a fully concatenated link-table - but it are definitely not the simplest approaches and especially the link-tables could lead to serious performance disadvantages. 

If you really want to keep by your approach you will probably need to extend your key-field to something like:

[Facility Code] & '|' & [Client ID] & '|' & [PERIOD]

whereby [PERIOD] means a common date- and/or period-field.

- Marcus