cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MojoGene Member
Member

Logical comparison between different columns?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Logical comparison between different columns?

@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

View solution in original post

4 REPLIES 4
Highlighted
ianbarker Regular Visitor
Regular Visitor

Re: Logical comparison between different columns?

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 Member
Member

Re: Logical comparison between different columns?

I'll give that a try ...

v-ljerr-msft Super Contributor
Super Contributor

Re: Logical comparison between different columns?

@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

View solution in original post

MojoGene Member
Member

Re: Logical comparison between different columns?

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!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 311 members 2,951 guests
Please welcome our newest community members: