Counting Strings Returned from an Iterating Measure

[vickyw]

Hi All, 

Hoping someone can help. 

I know columns (DAX or M) are not dynamic so in this situation I have opted to write three DAX measures to ensure my report output is always correct, regardless of when it was last refreshed however, I cannot get the 'Fails' to count.

Example - Fails card should show 12, but instead is 'Blank'

Here are the measures used in the above visual.

[E2E SLA] - calculates the working days between 2 dates (Case Opened/Case Closed ) or returns null if the case is still open

E2E SLA = 
SUMX(
    'All Cases',
    COUNTROWS(
        FILTER(
            'Date Table Static',
            'Date Table Static'[Date] >= 'All Cases'[Case Created Date] &&
            'Date Table Static'[Date] <= IF(ISBLANK('All Cases'[Case Closed Date]), TODAY(), 'All Cases'[Case Closed Date]) &&
            'Date Table Static'[Is Business Day] = TRUE()
        )
    )
)-1

[SLA Result] compares the above number of days to the target, and assigns labels 'Pass' or 'Fail' if it the case was completed in time.

SLA Result = 
IF(
    ISBLANK(MAX('All Cases'[Case Closed Date])), 
    BLANK(), 
    IF(
        [E2E SLA] > MAX('All Cases'[SLA Target]),
        "Fail",
        "Pass"
    )
)

In the card visuals:-

[SLA Count Pass] is working perfectly.

SLA Count Pass = 
CALCULATE(
    COUNTROWS('All Cases'),
    FILTER(
        'All Cases',
        'All Cases'[SLA Result] = "Pass"
    )
)

[SLA Count Fail] returns 'Blank' even though there are 12 rows where the [SLA Result] is 'Fail'

SLA Count Fail = 
CALCULATE(
    COUNTROWS('All Cases'),
    FILTER(
        'All Cases',
        'All Cases'[SLA Result] = "Fail"
    )
)

I've tested everything I can think of, but it appears to be that whatever string is in the 1st position of the [SLA Result] measure, I can't count or filter by it ☹️

Any help greatly appreciated

[v-zhengdxu-msft]

Hi @vickyw 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

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

[v-zhengdxu-msft]

Hi @vickyw 

There aren't any syntax error in this measure.
Actually that I create some sample data and test it, but unfortunately, the measure works fine in my side.

Please check if you apply some filter on the [SLA Result] measure?

If possible, please share your PBIX file(exclude sensitive data), so that we can help you better.

Best Regards

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