Error with COUNTROWS() not finding the right FIND() text in a table.

[Anonymous]

Hi All,

I am trying to find the amount of times a text appears in a table. I have a column = "Volume" from a table 'Table'. I want to see if volume is greater than or less than its 62 day standard deviation. If it is, make a column called 'Alert' with a phrase, if volume is not outside these bounds, the cell will be blank. Like so:

So I have an alert:

Alert_V% = 
    IF(
        [% Share Volume] > [YMax V%], "Share Volume: > 1.5 Std Dev", 
    IF(
        [% Share Volume] < [YMin V%], "Share Volume: < 1.5 Std Dev", BLANK()
    ))

and I want to essentially take this table, and count the number of times "Share Volume" appears and count those rows.

Alert Sum_V% = 
Var _session_date = MAXX(
    ALL('Verizon Call Reason'),'Verizon Call Reason'[session_date])
var _alert_table = SUMMARIZE(
    FILTER(ALL('Verizon Call Reason'),'Verizon Call Reason'[session_date]),
    'Verizon Call Reason'[call_reason_derived],"Measure",[Alert_V%]
    )
Return
COUNTROWS(FILTER(_alert_table,FIND("Share Volume: < 1.5 Std Dev",[Measure],,0)>0))

In above, I am making a table based on category, take the most recent date as a variable _session_date. Then make another variable which filters out the table based on the measure. Then I want to count those rows with the phrase in the return. 

I should get 7 (as from the table above with alerts shown), but instead get BLANK(). Why? I have attached the source in this link: PBIX Link 
 

@dedelman_clng has been a great help, and wanted to bring this back up. 

[AntrikshSharma · Accepted solution]

@Anonymous Like this: ?

Alert Sum_V% =
VAR _session_date =
    CALCULATE (
        MAX ( 'Verizon Call Reason'[session_date] ),
        ALL ( 'Verizon Call Reason' )
    )
VAR _alert_table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Verizon Call Reason', 'Verizon Call Reason'[call_reason_derived] ),
            "@Alert", [Alert_V%]
        ),
        ALL ()
    )
VAR Result =
    FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: > 1.5 Std Dev" )
    )
RETURN
    Result

For measure just wrap Result inside COUNTROWS ()

[Anonymous]

This works like a dream! One question, I also want to count the number of std ev the other way too. You filtered for

"Share Volume: > 1.5 Std Dev"

And I want to also filter for 

"Share Volume: < 1.5 Std Dev"

Would that be:

Alert Sum_V% =
VAR _session_date =
    CALCULATE (
        MAX ( 'Verizon Call Reason'[session_date] ),
        ALL ( 'Verizon Call Reason' )
    )
VAR _alert_table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Verizon Call Reason', 'Verizon Call Reason'[call_reason_derived] ),
            "@Alert", [Alert_V%]
        ),
        ALL ()
    )
VAR Result =
    CALCULATE(
       COUNTROWS(
         FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: > 1.5 Std Dev" )
    )
FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: < 1.5 Std Dev" )
    )
RETURN
    Result

Seeing if I can go this route?

[AntrikshSharma]

@Anonymous Do you mean 7 + rows for "Share Volume: < 1.5 Std Dev" ? then you can use:

Alert Sum_V% = 
VAR _session_date =
    CALCULATE (
        MAX ( 'Verizon Call Reason'[session_date] ),
        ALL ( 'Verizon Call Reason' )
    )
VAR _alert_table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Verizon Call Reason', 'Verizon Call Reason'[call_reason_derived] ),
            "@Alert", [Alert_V%]
        ),
        ALL ()
    )
VAR Result =
    FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: > 1.5 Std Dev" ) 
            || CONTAINSSTRING ( [@Alert], "Share Volume: < 1.5 Std Dev" ) 
    )
RETURN
    COUNTROWS ( Result )

If you prefer something that is more reusable then

Alert Sum_V% =
VAR _session_date =
    CALCULATE (
        MAX ( 'Verizon Call Reason'[session_date] ),
        ALL ( 'Verizon Call Reason' )
    )
VAR _alert_table =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Verizon Call Reason', 'Verizon Call Reason'[call_reason_derived] ),
            "@Alert", [Alert_V%]
        ),
        ALL ()
    )
VAR GreaterThan15 =
    FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: > 1.5 Std Dev" )
    )
VAR LessThan15 =
    FILTER (
        _alert_table,
        CONTAINSSTRING ( [@Alert], "Share Volume: < 1.5 Std Dev" )
    )
VAR Result =
    COUNTROWS ( GreaterThan15 ) + COUNTROWS ( LessThan15 )
RETURN
    Result

[Anonymous]

Thank you for your help, I did have an aside question - you made a variable for the maximum date in the query:

VAR _session_date =
    CALCULATE (
        MAX ( 'Verizon Call Reason'[session_date] ),
        ALL ( 'Verizon Call Reason' )
    )

 But this is never used again in the  rest of the formula. How does PowerBI/DAX know how to use the final date when never referenced after making the variable?

@AntrikshSharma 

Best,

John

[AntrikshSharma]

@Anonymous  That was just to replace the MAXX that you had in your formula. You can remove it if you don't need it.

[dedelman_clng]

Calling super DAX users - 

The behavior that is causing OPs problem is that when you run a SUMMARIZE and/or FILTER on the fact table and add measures as new columns, everything goes to 1, 0 or blank. Whereas if you take the fact table with the measures and put them into a simple table visual the values show properly.

I have attached a pbix that has left OP's code virtually untouched, but I created a table via SUMMARIZE, adding each of the individual measures that build up into the final measure. On the page "SU LOOK HERE", I've put the tables together side by side - Fact table with measures, and SUMMARIZE table.

I'm sure something is going on with row/filter/other context and this is where it gets muddy for me. Any help appreciated.

@Greg_Deckler  
@AntrikshSharma 
@vanessafvg  
@PaulDBrown 
@tex628  
@EricHulshof  
@Tahreem24  
@OwenAuger
@mahoneypat 
@nandukrishnavs 
@TomMartens