Cannot create single direction one-to-one relationship

[SevsBo]

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]

Hi @SevsBo 

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]

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

[MattiaFratello]

Hi @SevsBo, I believe the solution of @danextian works fine

[Bibiano_Geraldo]

Hi @SevsBo ,
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])
)

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: As you can, please avoid to use Many-to-Many Cardinality.

[SevsBo]

My problem is that I literally cannot select anything other than "Both" as a relationship direction when it's one-to-one.

[MattiaFratello]

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

[SevsBo]

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.