Measure to count info based on 2 columns

[RichOB]

Hi, in my scenario, I have a table with employees who have submitted helpdesk tickets. People submit them either for themselves or on behalf of someone else. I need the number of tickets opened on behalf of someone else, please.  

It is classed as a count of 1 if the name is different in the first 2 columns. If the cell is blank in the "On_Behalf_Of" column, it is classed as being the same person and does not need to be included in the count.

Table A is my scenario, and Table B is the result I'm looking for. 

Table A

Submitted_By	On_Behalf_Of	System
Dave D	Dave D	Email
Mark M	Bob B	Email
Frank F	Frank F	Email
Tom T	Tom T	Email
Sam S	Eric E	Email
Hank H	Hank H	Email
Paul P	Oliver O	Internet
Lloyd L	Lloyd L	Internet
Ian I	Ian I	Internet
Rich R	Carl C	Internet
Gary G		Internet
David D		Internet
Will W		Internet
Aaron A		Internet

Table B

System	Submitted_By	Behalf_of_Someone_else
Email	Mark M	1
Email	Sam S	1
Internet	Paul P	1
Internet	Rich R	1

Thanks

[grazitti_sapna · Accepted solution]

Hi @RichOB 

Use Below DAX 

Behalf_of_Someone_else =
COUNTROWS (
FILTER (
'Tickets',
NOT ISBLANK ( 'Tickets'[On_Behalf_Of] )
&& 'Tickets'[Submitted_By] <> 'Tickets'[On_Behalf_Of]
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Ashish_Mathur]

Hi,

Create this calculated column formula

Match = Data[Submitted_by]=Data[On_behalf_of]

Drag 2 columns to the Table visual and also this measure

Test = calculate(countrows(Data),Data[Match]=true)

Hope this helps.

[gmsamborn]

Hi @RichOB 

Wouldn't a calculated column like this work?

Behalf_of_Someone_else CC = 
IF(
    IF(
        ISBLANK( [On_Behalf_Of] )
            || [On_Behalf_Of] = "",
        [Submitted_By],
        [On_Behalf_Of]
    ) <> [Submitted_By],
    1
)

[v-mdharahman]

Hi @RichOB,

Thanks for reaching out to the Microsoft fabric community forum. The count you want is "tickets where On_Behalf_Of is not blank AND On_Behalf_Of is different from Submitted_By". You can use a measure or a calculated column to count it and as @anmolmalviya05, @Shahid12523 and @grazitti_sapna all have responded to your query, kindly go through their responses and check if your issue can be resolved.

I would also take a moment to thank @anmolmalviya05, @Shahid12523 and @grazitti_sapna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @RichOB,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @RichOB,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @RichOB,
We noticed there hasn’t been any recent activity on this thread. If you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[anmolmalviya05 · Accepted solution]

Hi @RichOB, Hope you are doing well!

You can achieve this with the help of DAX Measure, Please use the formula below:

Behalf_of_Someone_else =
IF (
NOT ISBLANK ( TableA[On_Behalf_Of] )
&& TableA[On_Behalf_Of] <> TableA[Submitted_By],
1,
0
)

If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.

Proud to be a Microsoft Fabric community super user

Let's Connect on LinkedIn
Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.

[Shahid12523]

IsBehalf =
IF (
NOT ISBLANK ( TableA[On_Behalf_Of] )
&& TableA[On_Behalf_Of] <> TableA[Submitted_By],
1,
0
)

[Shahid12523]

Behalf_of_Someone_else =
CALCULATE (
COUNTROWS ( TableA ),
TableA[On_Behalf_Of] <> BLANK (),
TableA[On_Behalf_Of] <> TableA[Submitted_By]
)

[grazitti_sapna · Accepted solution]

Hi @RichOB 

Use Below DAX 

Behalf_of_Someone_else =
COUNTROWS (
FILTER (
'Tickets',
NOT ISBLANK ( 'Tickets'[On_Behalf_Of] )
&& 'Tickets'[Submitted_By] <> 'Tickets'[On_Behalf_Of]
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!