cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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!




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

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?

@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

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

@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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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