Logical comparison between different columns?

[MojoGene]

The data in my PBI model are from a SQL database for Time & Billing in a professional services firm. I am attempting to create a report on "leverage," which is the sum of hours worked on a matter by timekeepers other than the timekeeper who originated the work. (All Timekeepers can be either originating timekeepers or working timekeepers.) Logically, it would be the SUM of [Hours] where working timekeeper <> originating timekeeper.

The problem arises because the identity of the originating timekeeper is in a lookup table listing all Matters, while the working timekeeper is identified in the FACT table for time entries (along with the Matter worked on, hours worked, etc.) The Matter table and the Fact table are related by the "Matter Number" field (one Matter to many time entries per Matter).

My initial thinking was a measure like this:

= CALCULATE(SUM(Fact[Hours]),Fact[TimekeeperID]<>Matter[OriginatingTimekeeperID])

But alas, I got the error message: "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Is there a way to perform a logical comparison (true/false) on different columns in different tables?

[v-ljerr-msft · Accepted solution]

@MojoGene

---

My initial thinking was a measure like this:

= CALCULATE(SUM(Fact[Hours]),Fact[TimekeeperID]<>Matter[OriginatingTimekeeperID])

But alas, I got the error message: "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Is there a way to perform a logical comparison (true/false) on different columns in different tables?

---

If you need to invoke CALCULATE using more than one column in the condition, you need to use FILTER function, which provides a list of values instead of a condition. Since these two tables are related by the "Matter Number" field, you can use RELATED() function to compare two column values.

Please refer to formula below:

=
CALCULATE (
    SUM ( Fact[Hours] ),
    FILTER (
        Fact,
        Fact[TimekeeperID] <> RELATED ( Matter[OriginatingTimekeeperID] )
    )
)

Regards,

Jerry

[Anonymous]

hey @v-ljerr-msft 

It is posible to use the function Related ( ) you use there but in a many to many relationship? 

Thanks.

[MojoGene]

Jerry:

That nailed it. I had been trying to wrap my mind around the RELATED function for the last day or so, but was finding this problem unusually challenging. 

Thanks!

[ianbarker]

I think you're almost there, however you need to use the SUMX function rather than SUM.

The SUMX will allow you to add an additional filter expression that the SUM won't.

[MojoGene]

I'll give that a try ...