Forum Discussion

CincyChi's avatar
CincyChi
New Member
2 years ago

Calculate/Count without numbers

Very new user to PowerBI and I am trying to see if there is a way to calculate on a term that isn't a numerical value.  The data that I am loading is in excel format, and the values in the columns are certain words.  My solution currently has been to create a template that has IF/THEN formulas which convert the words to a 1 or 0 which allows me to calculate the total number of a given term in a column.  I can then present these in the report view as various cards and allow the users of the report to adjust the slicers to see their desired metrics.  Does anyone no a way that I can manipulate/transform my data directly when loading into Desktop so that I can cut out the step of using my self built template using IF/THEN statements to translate the words to numerical values?  

Need Help

6 Replies

  • Anand24's avatar
    Anand24
    Super User
    2 years ago

    Hi CincyChi ,
    Can you please provide sample data and expected output/result?

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn
  • Shalini_T's avatar
    Shalini_T
    Frequent Visitor
    2 years ago

    CincyChi, Can you please share the sample data and the expected output? And what will be the values in the slicer?

    Regards,

    Shalini

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    When you say you built a template, what do you mean? Is it a supplemental file that you are joining to your data? You can use Power Query or DAX to create the logic to do this.

    • CincyChi's avatar
      CincyChi
      New Member
      2 years ago

      The file that is exported from our system contains words such as "submitted", "pending", "Past due", and this is in the form of an Excel file.  When I say template I mean that I simply add columns to the that file with IF/THEN formulas that convert the desired word, say "Submitted" in this example, to a 1 in a seperate column.  This then allows me to calculate the number of of items with "Submitted" in that column of interest.  I then add other IF/THEN formulas and seperate columns for all the other options in that column of interest, "Pending", "Past Due", etc.  I have built this "template" once and then everytime I export my data from our system I need to pass it through this template to make my calculations.  So I guess I am asking if there is a simple way in Power BI to have the system count a defined data element in a column that isn't a numerical value?

      • Anand24's avatar
        Anand24
        Super User
        2 years ago

        Hi CincyChi ,
        Not sure if you are looking for this but create a calculated column with below DAX:

        Word_Occurence =
        IF(
            CONTAINSSTRING(New_Table[Sentence], "completed") || CONTAINSSTRING(New_Table[Sentence], "past due") || CONTAINSSTRING(New_Table[Sentence], "pending"),
            1,
            0
        )

         

        Here's the result:

         

        Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

        Proud To Be a Super User !!!
        LinkedIn