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 Reason | Alert | Value | Alert | Value |
| WiFi | | | < 1.5 St Dev | 25 |
| Sales | > 1.5 Std Dev | 36 | | |
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!
