Calculate/Count without numbers

[CincyChi]

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?  

[audreygerred]

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]

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?

[audreygerred]

Yes, you can absolutely do this step in Power Query instead!

[Anand24]

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

[Shalini_T]

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

Regards,

Shalini

[Anand24]

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