DAX - Using SEARCH on a RELATED field in CALCULATETABLE

[oBi1]

Hi team

Can someone please me help me with this.

I'm using DAX to gerenate a subset of a table - using the following structure:

EVALUATE

ADDCOLUMNS(

CALCULATETABLE(

table1,

SEARCH("text",table1[column1],1,0)>0

),

"region", RELATED(table2[Region])

)

what I would actually like to do is also 'filter' on a column from the related table (table2). 

This table is related to table1 in the data model. My feable attempt was: to add

search("othertext",RELATED(table2[column2],1,0)>0

after the first SEARCH line (plus adding a , there obviously)

Any ideas as it fails...

Thanks!

Owen

[az38]

@oBi1 

are you sure you used this? there is no closing parentheses for RELATED()

search("othertext",RELATED(table2[column2] ) ,1,0)>0

[oBi1]

Yes sorry, that was a typo that I missed when typing my question.

The error message says: 

25/07/2020 14:28:08 Query (14, 41) The column 'table2[column2]' either doesn't exist or doesn't have a relationship to any table available in the current context.

[harshnathani]

Hi @oBi1 ,

The RELATED function requires that a relationship exists between the current table and the table with related information.

https://dax.guide/related/

Based on the error , can you confirm if there is an existing relationship.

If not then try using lookup.

https://dax.guide/lookupvalue/

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[oBi1]

confirmed there is a relationship between table1 and table2

it almost looks like DAX forgets that when inside the CALCULATETABLE statement...

Could this be a solution:

instead of doing CALCULATETABLE(table1, SEARCH(....

do: CALCULATETABLE ( table1, RELATEDTABLE(table2), and then start the searches.

not sure if this will hit me in the face in any other way (performancewise or any other hidden gem), but I feel that I need to reconfirm to the DAX engine what the tables are that are used before doing SEARCH functions etc.

[v-deddai1-msft · Accepted solution]

Hi @oBi1 ,

Please confirm the relationship between table 1 and table 2 and would you please try to use the following dax:

EVALUATE
SUMMARIZE (
    FILTER ( table1, SEARCH ( "text", table1[column1], 1, 0 ) > 0 ),
    "region", MAX ( table2[Region] )
)

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

Best Regards,

Dedmon Dai

[oBi1]

A really short version of my previous post which due to an internet error did not get posted:

* My Excel crashed before I could try the solution, rendering quite a bit of my work unusable;

* I ended up using another way around this by doing more combination work in Power Query and basically only DAX querying and filtering 1 table from the Data Model

Thanks so much for your willingness to reply

[v-deddai1-msft]

Hi @oBi1 ,

Thank you for sharing the information. Would you please accept useful reply as answer to help the other members find it more quickly?

Best Regards,

Dedmon Dai