RELATED and "The column either doesn't exist or doesn't have a relationship to any..."

[tfraletti]

Hello everyone, hope you're fine today!

I have 2 tables with a many to many active relationship and want to count the number of row in table A that doesn't have match on table B.

On other posts from Power Bi Community, got the following solution: IF(ISBLANK(RELATED('ZMMQ3020'[@ SKU Center Key]));FALSE();TRUE())

But i received the following message:  The column 'ZMMQ3020[@ SKU Center Key]' either doesn't exist or doesn't have a relationship to any table available in the current context.

But they have an active relationship. Any idea why this is happening and any other way to make it work?

Obs.: Use merge in Power Query isnt an option, since it grows my refresh time to more than 30 min because of the size of my data. Thanks in advance.

[MarcoPessina]

Hi @tfraletti ,

you can also use this measure:

HasMatch_Measure =

IF(

CALCULATE(COUNT(ZMMQ3020[@ SKU Center Key]), CROSSFILTER(Table1[@ SKU Center Key], ZMMQ3020[@ SKU Center Key] ,Both))>0,

TRUE(),

FALSE()

)

Hope it helps.

Cheers,

Marco

[tfraletti]

Hi Marco,
It almost worked...its strange. I needed to use it in two different tables that connect with my main one (main one is called ZMMQ3021). In one of them (ZMMQ3020), it connected properly and i could see on the new column from ZMMQ3021 table that values for true and falses exist, and also properly filter them. But when it comes to the other table (which i call ME5A), i also can see true and falses in the table, but when it comes to filter, i only see true values, even if i only use this column as the filter of my visual. Any ideia why this is happening?

[jstorm]

Try this:

COUNTROWS(
    FILTER(
        'Table A',
        NOT CONTAINS(
            'Table A', [SKU Center Key],
            'ZMMQ3020', [SKU Center Key]
        )
    )
)

[tfraletti]

Hi, it didn't work because PBI ask me values for the CONTAINS function.