Date Field not aligning for WpA & non-WpA data sets

[Olga_Karnas]

Hi, 

I have the following issue -- when two datasets, one a query from Workplace Analytics (Viva Insights) and one separate one -- are joined in PBI by a key other than a date, the date field cannot be used as a common axis (aka, it does not align). The only workaround I found was to create a separate dates table and use it to join the two datasets, however, the drawback of this is that this being an active connection, it prevents me from making connections through other keys. I checked all date formats etc., and those align for both data sets (rendering it possible to use date as a joiner key). I think that is not the case when two WpA datasets are joined. Any ideas on how to prevent this?

@Jeff_Kotz 

[Dazza]

Hi @Olga_Karnas!

Have you checked the Regional Settings for your Power BI file? In the past, I've had to ensure that this is set as per screenshot below to ensure decimals can be recognized etc. This could help align the date formats in your separate data sets... not sure if these settings apply to live connections, but might be worth a try. 

[Olga_Karnas]

hi @Dazza , thank you for this! I did check on that and unfortunately it wouldn't budge but I will explore further

[Kunaal_Bhagani · Accepted solution]

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. Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Docs. 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?