Forum Discussion
Create a Measure based on string value
Hello!
I'm starting to learn DAX but running into issues where I need to create measures based on STRING values. For example:
I need to count the number of occurances of the string "FTA" (Failure To Appear) in the Court Hearing Result column between a specified date starting and ending date range.
IN addidtion, I then need to make another meausre off of that expressing the percentages of FTA results based on the Total COUNT of hearings in that data range.
Can someone help me with these DAX formulas?
Thank you!
Kevykev Create a calculated column:
HasFTA := IF (Find("FTA",[Court Hearing Result],1,-1) = -1, 0, 1)
Create a new measure as FTACount = SUM([HasFTA]) in the same table where you created a new column "HasFTA"
Then a TotalRecords = CALCULATE(COUNTROWS(TABLE),ALL)
Percentages would be FTA% = [FTACount]/[TotalRecords]
Kevykev ,
YOu can create a measure based on string condition using FILTER DAX. Please follow the below DAX meausre to get your output:
Measure = CALCULATE(COUNT('Table'[Court Hearing Result]),FILTER(ALL('Table'),'Table'[Court Hearing Result]="FTA"),DATESBETWEEN('Table'[DateColumn],FIRSTDATE('Table'[DateColumn]),LASTDATE('Table'[DateColumn])))Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.Thank you very much. This helps me greatly be getting an example that I can now see applied to my data model and business use case!
Thank you very much. This helps me greatly be getting an example that I can now see applied to my data model and business use case!
4 Replies
Kevykev Create a calculated column:
HasFTA := IF (Find("FTA",[Court Hearing Result],1,-1) = -1, 0, 1)
Create a new measure as FTACount = SUM([HasFTA]) in the same table where you created a new column "HasFTA"
Then a TotalRecords = CALCULATE(COUNTROWS(TABLE),ALL)
Percentages would be FTA% = [FTACount]/[TotalRecords]
Thank you very much. This helps me greatly be getting an example that I can now see applied to my data model and business use case!
Kevykev ,
YOu can create a measure based on string condition using FILTER DAX. Please follow the below DAX meausre to get your output:
Measure = CALCULATE(COUNT('Table'[Court Hearing Result]),FILTER(ALL('Table'),'Table'[Court Hearing Result]="FTA"),DATESBETWEEN('Table'[DateColumn],FIRSTDATE('Table'[DateColumn]),LASTDATE('Table'[DateColumn])))Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.Thank you very much. This helps me greatly be getting an example that I can now see applied to my data model and business use case!