ALLEXCEPT for non directly related tables

[Gazi]

Hi community!

I am stuck with calculating AVG count of children in hierarchy 😞 

My DAX code example (for 1 Level):

AVG AA = 
IF (
    [BrowseDepth] = 1,
    CALCULATE (
        COUNTA ( ArticleAttribute[ArticleAttributeID] ),
        ALLEXCEPT ( ArticleAttribute, StuctureGroupHierarchy[Level 1] )
    )
        / CALCULATE ( DISTINCTCOUNT ( StuctureGroupHierarchy[Level 2] ) )

But DAX throws this error: 

I have 4 tables:

Mapping_table       Article table 

ArticleRevisionID *:1 ArticleRevisionID

Article table          ArticleAttribute table 

ArticleRevisionID 1:* ArticleRevisionID

Please help! 

Gazi

[v-yuta-msft · Accepted solution]

Hi Gazi ,

A simple way is to merge ArticleAttribute, Acticle and Mapping.Table as a new table then create one-many relationship between StructureGroupHierarchy and the new table.

Regards,

Jimmy Tao

[v-yuta-msft]

Hi Gazi，

In your diagram, table 'StuctureGroupHierarchy' and table 'Mapping_table' has one-many relationship, table 'Mapping_table' and table 'Article' is  many-one relationship so that 'StuctureGroupHierarchy' and 'Article' is many-many relationship, so relationship between 'StuctureGroupHierarchy' and 'ArticleAttribute' can be one-many.

In addtion, as the error shows, table in the second param shoud be one-many or one-one relationship with table in the first param. So you should create a one-many relationship between the two tables.

Regards,

Jimmy Tao

[Gazi]

Hi Jimmy! @v-yuta-msft

Thank you for your reply! Can you please specify how can I create that one-many relationship you said??

I've tried to think about it, but apparently I am not getting something...

Thank you a lot!!

Gazi