DISTINCTCOUNT() of name without a record that CONTAINS()

[MickyG]

I want to get the distinct count of the StudentName that DOES NOT contain a record with the keyword "Withdrawn". So the DAX formula result for the data below should have a  single count for each student's name except for Richard Gear because he has the keyword "Withdrawn" in one of his records. Also, there is a calendar table. If the formula can include filters by date so I can use a slider. The records should be within the MIN() and MAX() of the Calendar Table.

DATA:

RESULT:

[Ashish_Mathur]

Hi,

Create a calculated column formula

Test = search("Withdrawn",Data[Results],1,0)

To your visual, drag Name and this measure

Measure = calculate(countrows(Data),Data[Test]=0)

Hope this helps.

[Anonymous · Accepted solution]

Hi  @MickyG ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _min=MINX(ALLSELECTED('Date'),[Date])
var _max=MAXX(ALLSELECTED('Date'),[Date])
return
IF(
CONTAINSSTRING(MAX('Table'[Result]),"Withdrawn")=FALSE()&&
MAX('Table'[Exam Date])>=_min&&MAX('Table'[Exam Date])<=_max,1,0)

Measure =
CALCULATE(DISTINCTCOUNT('Table'[StudentName]),
    FILTER(ALLSELECTED('Table'),
    'Table'[StudentName]=MAX('Table'[StudentName])))

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly