Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |