Forum Discussion
Calculate/Count without numbers
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.
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?
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 !!!
LinkedInYes, you can absolutely do this step in Power Query instead!
