Create a Measure based on string value

[Kevykev]

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!

[Anonymous · Accepted solution]

@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] 

[Tahreem24 · Accepted solution]

@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.

[Kevykev · Accepted solution]

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!

[Tahreem24 · Accepted solution]

@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.

[Kevykev · Accepted solution]

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!

[Anonymous · Accepted solution]

@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 · Accepted solution]

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!