Forum Discussion
Date Field not aligning for WpA & non-WpA data sets
If my understanding of the issue is correct I think the PBI templates from MS should have followed best practice and have a calendar table to which the measures apply the filters and subsequently propagate those filters to other tables. Without this, by joining any other fact table to the Viva Insights table e.g. person query would create a many to many relationship. So, you may need to either have an intermediary table (old method) or use the Many to Many relationship (new method). That being said I haven't tried to do this. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships. Might need a product owner to better answer this.
Note: Even createing a seperate date table I think would require modifiing all the measures as they apply the filters to the Viva Insights table e.g. person query. With respect to "prevents me from making connections through other keys" the could be the option to maybe create a new column with a custom key that includes the date and whatever other dimenion you wanted to add?
If my understanding of the issue is correct I think the PBI templates from MS should have followed best practice and have a calendar table to which the measures apply the filters and subsequently propagate those filters to other tables. Without this, by joining any other fact table to the Viva Insights table e.g. person query would create a many to many relationship. So, you may need to either have an intermediary table (old method) or use the Many to Many relationship (new method). That being said I haven't tried to do this. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships. Might need a product owner to better answer this.
Note: Even createing a seperate date table I think would require modifiing all the measures as they apply the filters to the Viva Insights table e.g. person query. With respect to "prevents me from making connections through other keys" the could be the option to maybe create a new column with a custom key that includes the date and whatever other dimenion you wanted to add?