Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUMMARIZE error - The Expression refers to multiple columns.

Good Morning.  

 

I have a connection between PowerBI and Jira (our internal help desk) and through that connection, our service types are passed over but are passed over as a code vs. the actual type.  In PowerBI, I created a new column doing a Text.Replace to convert those codes to actual names and I am doing that through Power Query Builder and what I have left is a new column in my table called ServiceType and it is of data type Text.  I want to get a count for each of those ServiceType to throw into a pie chart on my report but I keep getting the noted error.  I have used this line for other groupings and it has worked until this one column.  The code I am using is:  

ServiceTotals = SUMMARIZE(Issues, Issues[ServiceType], "Service Types", COUNT(Issues[ISSUE_KEY]))
 
Why does this work on other fields and not this one?  The only thing I can easily come up with is the other SUMMARIZE calls I am doing are on measures vs. the actual table column so I don't know if I need to convert this column to a measure (not sure the best way to do a simple replicate in a measure if that is the case) or if there is something I am missing.  

Thanks.
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Ok, finally figured it out that I wasn't trying to build the summary as a table.  Not sure how I missed it before but I finally caught it and I have it working now.  

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , ideally this should work

    ServiceTotals = SUMMARIZE(Issues, Issues[ServiceType], "Service Types", COUNT(Issues[ISSUE_KEY]))

     

    Can you share a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yeah, that is what I thought too as I am using that same code 2 other times to show pie charts.  

      I have not had to share a PBIX file before, what data actually comes over with the file?  The data that is there isn't sensitive as it is just ID numbers, names and company names, no passwords or confidential data.  I just don't know if the PBIX file includes connections with passwords to the db's.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Let's see if this helps any.  Attached is a screenshot of my data showing the key column, the original pull from the Jira platform with raw data (Customer_Request_Type) and my new column (ServiceType).  This is from the Power Query Builder.

      

     

    Here is the code I used to convert what is in Customer_Request_Type to what you see in the column ServiceType:  

     

    ServiceType = Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Customer_Request_Type], "isdd/461d0f58-1793-429b-a5e6-e043252915f2","License Request"), "isdd/8781bf07-ccfd-48fc-a2e6-4247e4b96672", "iMIS or CRM Issues"), "isdd/a04cd085-d7cd-43ff-9b77-44593049cf07", "Zoom/Ring Central Issue"), "isdd/compsupport", "Device Support"), "isdd/d0134297-ed98-433f-9d9e-8c746f31b563", "Report Requests"), "isdd/fd1d8a0c-59bb-44f9-ac49-94994a3fdb74", "Computer Program Support"), "isdd/getithelp", "Email Submittal"), "isdd/guestwifi", "Wi-Fi/Internet Support/VPN"), "isdd/newaccount", "User Account Management"), "isdd/newsoftware", "Request New Stuff"), "isdd/4d19144d-4a5a-49e3-8d4d-2ab756f013cb", "Request MELG Kit/Zoom Cart"), "isdd/bd10d23a-1166-4abd-afe4-94cdeed9bc7a", "Onboard Employee"), "isdd/da0434d1-42e6-4c71-bfad-29ea96d7675a", "Offboard Employee")

     

    Kind of ugly but I have no other way to get this from the Jira PowerBI Connector as this is a custom field and that connection is limited.  I have to assume the problem is based on how this column ServiceType was created and that is why the Summarize isn't working so I am open to suggestions.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Oh, and the other thing I tried was to do a substitute as a measure in using the Customer_Request_Type field thinking that might be workable in place of the new column and that failed with the same error so I believe the problem starts with the original data coming from Jira.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Ok, finally figured it out that I wasn't trying to build the summary as a table.  Not sure how I missed it before but I finally caught it and I have it working now.  

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    It’s glad to hear that you have solved the problem. Would you like to share some experience or just mark your own reply as a solution so that others can learn from this too?

    Thanks in advance!

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.