Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Measure to count info based on 2 columns

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_ByOn_Behalf_OfSystem
Dave DDave DEmail 
Mark MBob BEmail 
Frank FFrank FEmail 
Tom TTom TEmail 
Sam SEric EEmail 
Hank HHank H Email 
Paul POliver OInternet
Lloyd L Lloyd LInternet
Ian IIan IInternet
Rich RCarl CInternet
Gary G Internet
David D Internet
Will W Internet
Aaron A Internet

Table B
SystemSubmitted_ByBehalf_of_Someone_else
Email Mark M1
Email Sam S1
InternetPaul P1
InternetRich R1

Thanks
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    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!

  • anmolmalviya05's avatar
    anmolmalviya05
    1 year ago

    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.

10 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    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!

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

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

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

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

  • anmolmalviya05's avatar
    anmolmalviya05
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    1 year ago

    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
)

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.