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

No output when using FIND() for text data

Hi All,

 

Trying this again. But I am having an issue trying to count the rows of a table I made using the FIND command. I want to find certain types of words, count the rows which they appear, and output my total sum to a card - which will be emailed as an alert. 

 

I have a couple formulas. My first formula:

 

 

 

 

 

 

Alert_CRT = 
    IF(
        [Avg Volume] > [YMax Volume], " > 1.5 Std Dev", 
    IF(
        [Avg Volume] < [YMin Volume], " < 1.5 Std Dev", BLANK()
    ))

 

 

 

 

 

 

 

Sees if today's volume is larger than a 1.5 standard deviation calculation bound (YMin, YMax - A calculation of a categories 3 month standard deviation). If above say > 1.5 Std Dev, if below = < 1.5 Std Dev. Otherwise be blank.

 

This populates a table that looks like:

 
 DATE 8/1 Date 8/2 
Call ReasonAlertValueAlertValue
WiFi  < 1.5 St Dev25
Sales> 1.5 Std Dev36  

 

The calculation for the sum is as followed: 

Take the most recent session date as the VAR _session_date, Group a table as the VAR _alert_tables, which is every category, by the most previous day, and filter on the measure (stated above). Then RETURN the counted number of rows which say "Std Dev", part of the formula above. 

 

 

 

 

 

 

Alert Sum_CRT = 
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]=_session_date),
    'Verizon Call Reason'[call_reason_derived],
    "Measure",[Alert_CRT]
    )
Return
COUNTROWS(FILTER(_alert_table,FIND("Std Dev", [Measure],,0)>0))

 

 

 

 

 

 

 

When I use this I  get 'BLANK()' instead of '1' - from the table attached representing 8/2. Am I using FIND() wrong, or compressing table poorly? 

 

@dedelman_clng has been a good support so far, and wanted to bring this back.

 

Thank you!

1 REPLY 1
Highlighted
Super User I
Super User I

Hello @Langutang ,

 

I believe you have posted a similar problem on the following thread:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Error-with-COUNTROWS-not-finding-the-right-FI...

 

Please confirm if your issue has been resolved.

 

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

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