topic Re: RELATED () function error in DAX Commands and Tips

RELATED () function error

MarkKemeny — Fri, 10 Feb 2023 16:01:22 GMT

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

Re: RELATED () function error

johnt75 — Fri, 10 Feb 2023 16:18:45 GMT

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.

Re: RELATED () function error

v-zhangti — Mon, 13 Feb 2023 02:21:55 GMT

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.