countrows with multiple filter values selected

[aragu]

I have a matrix with Project Testpass as column, Name_module and Name_metric as rows with P90_pass as values. I have slicer filter values for collected date and name_testpass. I would like to cound the number of Name-metric where the P90_pass value = "FAIL". The formula below only works for 1 name_testpass value but when i selected multiple name_testpass values for my slicer, it does not give the correct count.

@tamerj1 @technolog @eliasayyy  @OwenAuger 

Fail_Count =
VAR SelectedTestpasses = VALUES('Platform_time'[Name_Testpass])
VAR FilteredData =
FILTER(
ALL('Platform_time'),
[P90_pass] = "FAIL" &&
CONTAINS('Platform_time', 'Platform_time'[CollectedDate], 'Platform_time'[CollectedDate]) &&
CONTAINS('Platform_time', 'Platform_time'[Name_Testpass], 'Platform_time'[Name_Testpass]) &&
'Platform_time'[Name_Testpass] IN SelectedTestpasses &&
CONTAINS('Platform_time', 'Platform_time'[Name_Module], 'Platform_time'[Name_Module])
)
RETURN
CALCULATE(
COUNTROWS(
SUMMARIZE(
FilteredData,
'Platform_time'[Name_Metric]
)
),
ALLSELECTED('Platform_time'),
'Platform_time'[Name_Testpass]
)

[technolog]

Hi @aragu !

Let's try do it step-by-step.

Capture the Selected Test Passes:
Begin by capturing the values of Name_Testpass that have been selected in the slicer.
VAR SelectedTestpasses = VALUES('Platform_time'[Name_Testpass])

Filter the Data:
Filter the entire 'Platform_time' table to only include rows where:
The P90_pass column has a value of "FAIL".
The Name_Testpass column's value is among the selected test passes.
VAR FilteredData =
FILTER(
ALL('Platform_time'),
'Platform_time'[P90_pass] = "FAIL" &&
'Platform_time'[Name_Testpass] IN SelectedTestpasses
)

Count Unique Metrics:
From the filtered data, summarize by the Name_Metric column to get unique metrics. Then, count the number of rows in this summarized table, which will give the count of unique metrics with a "FAIL" value for the selected test passes.
COUNTROWS(
SUMMARIZE(
FilteredData,
'Platform_time'[Name_Metric]
)
)

Final Formula:

Combining the above steps, the complete DAX formula is:

Fail_Count =
VAR SelectedTestpasses = VALUES('Platform_time'[Name_Testpass])
VAR FilteredData =
FILTER(
    ALL('Platform_time'),
    'Platform_time'[P90_pass] = "FAIL" &&
    'Platform_time'[Name_Testpass] IN SelectedTestpasses
)
RETURN
COUNTROWS(
    SUMMARIZE(
        FilteredData,
        'Platform_time'[Name_Metric]
    )
)

This formula will provide the desired count of unique Name_Metric values where P90_pass is "FAIL" for the selected Name_Testpass values.

Perhaps my colleagues will suggest a different solution @tamerj1 @annonymous1999  @OwenAuger 

[aragu]

thank you @technolog...i tried but it did not seem to fix it. for most of the measures the original formula works even with multiple filter values. there were only some that did not. so perhaps it is not a DAX issue but some other dependencies from my data structure??? I calculate P90_pass with this formula, essentially comparing my calculated P90 to a target value. I did not use the built-in percentile formula since it gives a different answer. Will the below formula (since it calculates based from filter values too) impact the FAIL COUNT formula?

P90_pass =

VAR NonBlankTime =

    FILTER('Platform_time', NOT(ISBLANK([MetricValue])))

VAR SortedNonBlankTime =

    ADDCOLUMNS(NonBlankTime, "Time_Sorted1", Platform_time[MetricValue])

VAR TotalRows =

    COUNTROWS(SortedNonBlankTime)

VAR NinetyPercentileIndex =

    CEILING(0.90 * TotalRows, 1)

VAR P90_value =

    IF(

        NinetyPercentileIndex <= TotalRows,

        MAXX(TOPN(NinetyPercentileIndex, SortedNonBlankTime, [Time_Sorted1], ASC), [Time_Sorted1]),

        BLANK()

    )

VAR TargetValue = MIN('Platform_time'[P90Target])

RETURN

    IF(

        NOT(ISBLANK(P90_value)) && P90_value < TargetValue,

        "PASS",

        "FAIL"

    )

[technolog]

@aragu there are can be some issues with this.

The P90_pass formula is contingent upon the prevailing filter context. When utilized as a calculated column, it evaluates the value for each table row. As a measure, its computation is influenced by the filter context set by visuals, slicers, or other measures.

The Fail_Count formula is also influenced by the current filter context. A potential context disparity between the P90_pass and Fail_Count calculations can yield unanticipated outcomes.

The data structure might harbor dependencies or intricacies that influence the results. Proper configuration of table relationships and the absence of unexpected filters are paramount.