cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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:

 

notes_1.PNG










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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

@Langutang Like this: ?

1.PNG

 

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 ()

1.PNG

1.PNG

 


Thank you,
Antriksh Sharma

View solution in original post

6 REPLIES 6
Highlighted
Super User I
Super User I

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 

 




Proud to be a Super User!




Highlighted
Super User I
Super User I

@Langutang Like this: ?

1.PNG

 

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 ()

1.PNG

1.PNG

 


Thank you,
Antriksh Sharma

View solution in original post

Highlighted

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?

Highlighted

@Langutang 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

Thank you,
Antriksh Sharma
Highlighted

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

Highlighted

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


Thank you,
Antriksh Sharma

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors