Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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.
Proud to be a Super User! | |
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?
Yes, you can absolutely do this step in Power Query instead!
Proud to be a Super User! | |
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 !!! |
@CincyChi, Can you please share the sample data and the expected output? And what will be the values in the slicer?
Regards,
Shalini