Forum Discussion

aragu's avatar
aragu
Microsoft Employee
2 years ago

countrows with multiple filter values selected

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 t...
aragu's avatar
aragu
Microsoft Employee
2 years ago

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's avatar
technolog
Super User
2 years ago

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.