dax query related error

[domtrump]

I am new to writing DAX queries. I started with this simple one as a test. (I am writing this in DAX Studio as a test.)

/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
    PennyPurge[AccountID],
    "Ldate",related(ASTData[LastDatePlayed])
)
ORDER BY 
    PennyPurge[AccountID] ASC,
    ASTData[LastDatePlayed] ASC
/* END QUERY BUILDER */

In my data model, ASTData is a lookup table and DOES have a 1 to many relationship to PennyPurge. Just running this simple query results in the follong error:

Why would it say there is no relationship when there absolutely is? What is meant by "the current context?" The field definitely exists. Here is a screenshot from the model:

Like I said, new to this so please let me know if more information is needed (short of recreating the tables). Thanks.

[v-yilong-msft]

Hi @domtrump ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

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

[sjoerdvn]

You are getting this error because you're using STData[LastDatePlayed] in the ORDER BY and it's not in the column list of the SUMMARIZECOLUMNS.
Also, you can not use RELATED like that:

• The RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function. A table scanning function, such as SUMX, gets the value of the current row value and then scans another table for instances of that value.

[AnalyticPulse]

instead of writing it in dax studio did you tried it in powerbi itself?

[domtrump]

Not currently using Power BI. Just PowerPivot in Excel. My goal is to query directly out of the data model but using a filter from the lookup table.

[domtrump]

Tried this as well, but get the same error.

/* START QUERY BUILDER */
EVALUATE
NATURALLEFTOUTERJOIN ( PennyPurge, ASTData )
evaluate
SUMMARIZECOLUMNS(
    PennyPurge[AccountID],
    "Ldate",related(ASTData[LastDatePlayed])
)
ORDER BY 
    PennyPurge[AccountID] ASC,
    ASTData[LastDatePlayed] ASC
/* END QUERY BUILDER */