Forum Discussion

MarkKemeny's avatar
MarkKemeny
New Member
3 years ago
Solved

RELATED () function error

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's avatar
    johnt75
    3 years ago

    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's avatar
    v-zhangti
    3 years ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
    v-zhangti
    Community Support
    3 years ago

    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.