SSAS Tabular Model - Dealing with date and 30 minute time interval

Question

I've created an SSAS Tabular Model in which I have two different tables, AGENT and CHAT, that I would like to be related in order to filter and group by DATE and a 30 minute time interval field called INTERVAL in a Power BI report hierarchy.

Both tables are related to a DATE table with unique date values. In Power BI I have a table that contains measures from both tables but the filter does not work by interval but it does by date as they are related.

How can I get through this being efficient in the model?

Answer 1

The SSAS Tabular Model doesn't allow Many:Many relationships, I would never be able to join the AGENT and CHAT tables.

SOLUTION I found:

Create a view in the SQL Server Database that contains all the data from the AGENT and CHAT tables by using a FULL OUTER JOIN.

Whenever a NULL value appeared in the DATE or in the INTERVAL column I selected the correspondent non-null value from either the AGENT or CHAT table. This was only possible thanks to both tables having the same values for the intervals.

Having the view I was able to filter all the data as needed.