RELATED () function error

[MarkKemeny]

Hi Guys,
I have a problem with the RELATED() funcion in dax.
I have a simple dimension and fact table where I have to compare date values in the fact table with the values in the dimension table:
CALCULATE(COUNTROWS('fct_Overview_Status_Phase_1-2-3_Promotion'), 'fct_Overview_Status_Phase_1-2-3_Promotion'[Date.1]<RELATED(dim_Deadlines[Deadline])).
It gives me the error that "The column 'dim_Deadlines[Deadline]' either doesn't exist or doesn't have a relationship to any table available in the current context."

The two tables are connected with a 1 to many relationship so I dont understand what is the problem.
When I create a conditional column in the fact table using the RELATED() function it works fine, but when I am using that inside a DAX measure it gives me this error.
Thanks,

Mark

[johnt75 · Accepted solution]

At the point where you are using RELATED no row context exists, so it cannot traverse the relationship. I think you want something like

My Measure =
VAR MaxDate =
    MAX ( dim_Deadlines[Deadline] )
RETURN
    CALCULATE (
        COUNTROWS ( 'fct_Overview_Status_Phase_1-2-3_Promotion' ),
        'fct_Overview_Status_Phase_1-2-3_Promotion'[Date.1] < MaxDate
    )

As long as some column from your dimension table is in the visual this should work.

[v-zhangti · Accepted solution]

Hi, @MarkKemeny 

You can try the following methods.
Sample data:

One to many

Measure = 
CALCULATE ( COUNTROWS ( 'fct_Overview_Status_Phase_1-2-3_Promotion' ),
    FILTER ( ALL ( 'fct_Overview_Status_Phase_1-2-3_Promotion' ),
        'fct_Overview_Status_Phase_1-2-3_Promotion'[Date.1]
            < SELECTEDVALUE ( dim_Deadlines[Deadline] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75 · Accepted solution]

At the point where you are using RELATED no row context exists, so it cannot traverse the relationship. I think you want something like

My Measure =
VAR MaxDate =
    MAX ( dim_Deadlines[Deadline] )
RETURN
    CALCULATE (
        COUNTROWS ( 'fct_Overview_Status_Phase_1-2-3_Promotion' ),
        'fct_Overview_Status_Phase_1-2-3_Promotion'[Date.1] < MaxDate
    )

As long as some column from your dimension table is in the visual this should work.