Find which dax columns are referring specific column?

[Anonymous]

Hi,

Is there any way if I can find which columns in other(or same) data tables are referring this column? 

I can find manually clicking all other columns seeing dax but wondering any list shown in function. 

[danextian · Accepted solution]

Hi @Anonymous 

Your DAX does not return a scalar value which is expected of a calculated column. I can't see your formula but these are sample formulas that will return such an error as they do not return a single value

=TableName

=FILTER(TableName, TableName[Column1] = "A)

to resolve this, the column for each row must return a single value

= COUNTROWS( TableName ) --returns the row count

= MAXX ( FILTER(TableName, TableName[Column1] = "A),TableName[Column1] ) --returns the max value of Column1 after Column1 is filtered down to A

[OwenAuger · Accepted solution]

Hi @Anonymous 

Within Power BI Desktop, in DAX Query view (or tools such as DAX Studio) you can use the DAX function INFO.CALCDEPENDENCY to query dependencies in the model, 

For example, this query returns a table containing all dependencies in the model.

EVALUATE
INFO.CALCDEPENDENCY ( )

This more specific query would return calculated columns that depend on the column YourTable[NDPd/Non NDPd]:

EVALUATE
FILTER (
    INFO.CALCDEPENDENCY ( "OBJECT_TYPE", "CALC_COLUMN" ),
    "REFERENCED_TABLE" = "YourTable" -- change to correct table name
        && "REFERENCED_OBJECT" = "NDPd/Non NDPd"
)

These return immediate dependencies, but I believe some clever folks have written more sophisticated queries to recurse through dependencies. If I can find an example I'll post here 🙂

You can also use Tabular Editor to examine dependencies for any object.

[Magudeswaran_MR]

Thanks for sharing the INFO Function use on Query Dependencies

[danextian · Accepted solution]

Hi @Anonymous 

Your DAX does not return a scalar value which is expected of a calculated column. I can't see your formula but these are sample formulas that will return such an error as they do not return a single value

=TableName

=FILTER(TableName, TableName[Column1] = "A)

to resolve this, the column for each row must return a single value

= COUNTROWS( TableName ) --returns the row count

= MAXX ( FILTER(TableName, TableName[Column1] = "A),TableName[Column1] ) --returns the max value of Column1 after Column1 is filtered down to A