How to Count on an Inactive Relationship without using Userelationship

[muturuf]

Hi All

I have a very simple problem that got me scracthing my head.

I have two tables one filtering the other 1 to * on an inactive relationship

is there way to count the related rows from the Employee table against the projects without using UseRelationship?
(and I am almost certain that I need to use RELATEDTABLE but couldn't figure out how.)

The measure I use is the following which works but I don't want to use it from efficiency standpoint.

COUNTROWS (

    FILTER (

        CROSSJOIN ( 'Project', 'Employee' ),

        'Project'[ProjectId] = Employee'[Project]

    )

)

desired ouput:

[OwenAuger · Accepted solution]

Hi @muturuf 

Generally, the best way to implement "virtual relationships" is with TREATAS.

See this article:

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

In this case, it would look like:

CALCULATE (
    COUNTROWS ( 'Employee' ),
    TREATAS (
        VALUES ( 'Project'[ProjectId] ),
        'Employee'[ProjectId]
    )
)

Out of interest, why do you want to avoid USERELATIONSHIP in this case?