How to apply a same date filter in N linked tables where date is not part of the relationship key?

[Peter_]

Hi,

How can I apply a same date column filter in N linked tables where the date is not used as a key? I have N tables, a primary table and N-1 secondary tables that are Cross linked (in both directions) to the primary table on a unique key that is each specific to a secondary table and doesn't contain the date. The secondary tables are just lookup tables for other columns. Both primary and secodnary tables have a date column.

Example scenario:  can I make Power Bi select Blue or Red color from the secondary table 2 below based on selected date in the primary table? Or create a line chart with X axis being the date, legend being the color and values being some calculated count for each date based of other secondary column?

Primary table

Date     | Key 1 | Key 2 | Key 3  | .... | Key N - 1

[date1] |   1      |   5      |  1111 | ...

[date1] |   2      |   3      |  1111 | ...

[date2] |   111  |   5      |   2      | ...

Secondary table 1

Date     | Key 1 |  XYZ column |....

....

Secondary table 2

Date     | Key 2 | Color | YZX column | ...

[date1] |  5       | Blue   | ...

[date1] |  1       | Green| ....

[date2] |  5       | Red    | ...

...

Secondary Table N -1 

...

One way to solve this I can think of is to include the date in the relationship key, but the problem is that PowerBi allows selecting only a single column as a key. I'd have to create a calculated key column as {[date]+[Key]} but that doesn't work in my case as that would grow the dataset beyond acceptable limits. The reason behind this design was to encode the actual columns behind hash ids/keys, which are much smaller in size than actual column values, into primary table as keys and then use the secondary tables as lookup tables so as to significantly lower the size of the whole dataset and be at ~ 5GB of size. To further complicate the case the dataset is composed of multiple such encoded primary + secondary tables per date. So the unique key between primary and secondary tables is really the [date] + [key].

Is above possible to solve in any way while retaining the encoding in place? As I said without encoding the dataset is too large (~50+GB) for PowerBi.

Thanks for any pointers!

Peter

[Stachu]

do you have a separate calendar table that is linked by date with your dimension tables?
I think that's the easiest approach

otherwise you can try with these patterns:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[Peter_ · Accepted solution]

That wouldn't work as the date was a sub-key defining the true mapping between dataset and a dictionary. Moving the date out of dictionaries would make the hashes alias. I solved it by re-encoding the whole dataset every time it needs to be updated with data from a new date and thus there's no need for dates in dictionaries any more. The hashes are unique across all dates.