Cannot create single direction one-to-one relationship

Question

I have created a relationship between a generic Date table and a table that containst a Date column. The relationship is identified as one-to-one, given that the date data is quite short and distinct in the other table, so this is correct.

The issue I have is that the Cross-filter direction is automatically set to Both and I do not want that as I want to connect that secondary table further to other tables, which are already linked to the Date table.

Is there a way to resolve that without artificially changing the relationship type to Many-to-Many?

PS: I have auto-detect relationships OFF.

danextian · Answer

Hi SevsBo&nbsp;
If each table has a distinct set of unique dates, then a many-to-many relationship isn't necessary. To set the cross-filter direction to single, use either a one-to-many or many-to-one relationship. Dragging the relationship from Table1 to Table2 will create a flow in that direction.

sevsbo · Answer

Because it only has a handful of dates - starts of each month to be precise. I need to filter this, along with other data at the same time, on a monthly level so I need a separate date table to control that.

mattiafratello · Answer

Hi&nbsp;SevsBo, may I ask you why you need a Date Table if your fact table has already unique dates?

bibiano_geraldo · Answer

Hi&nbsp;SevsBo&nbsp;,you can override the filter behavior using DAX measures. Instead of relying on automatic relationship propagation, use TREATAS() or USERELATIONSHIP() to explicitly control filtering when needed.using TREATAS() your DAX will look like this:
CALCULATE(
    SUM(YourTable[YourColumn]),
    TREATAS(VALUES(DateTable[Date]), YourTable[DateColumn])
)

Example using USERELATIONSHIP() (if you create an inactive relationship):
CALCULATE(
    SUM(YourTable[YourColumn]),
    USERELATIONSHIP(DateTable[Date], YourTable[DateColumn])
)

&nbsp;
Otherwise you will have to change the cardinality to Many to one or One to many, and choose the cross-filter direction to single:

Note:&nbsp;As you can, please avoid to use Many-to-Many Cardinality.

sevsbo · Answer

That's exactly what I did but ended up in this situation. It will not allow a single direction relationship, only Both.

Forum Discussion

Cannot create single direction one-to-one relationship

7 Replies

Related content

One to Many Relationship Problem

Filtering on visuals to show 'no matches' on one-to-many relationships.

One to One Relationships and single direction

Vertipaq Analyzer not able to report on memory consumption on models using “direct lake on Onelake”

Left Join in Direct Query mode with one-to-many relationship