topic Re: Error with COUNTROWS() not finding the right FIND() text in a table. in DAX Commands and Tips

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

Anonymous — Mon, 28 Sep 2020 00:28:28 GMT

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.

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

dedelman_clng — Mon, 28 Sep 2020 13:14:02 GMT

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
Anonymous
Tahreem24
OwenAuger
mahoneypat
nandukrishnavs
TomMartens

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

AntrikshSharma — Mon, 28 Sep 2020 13:44:12 GMT

Anonymous Like this: ?

For measure just wrap Result inside COUNTROWS ()

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

Anonymous — Mon, 28 Sep 2020 14:43:41 GMT

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?

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

AntrikshSharma — Mon, 28 Sep 2020 14:52:29 GMT

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

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

Anonymous — Mon, 28 Sep 2020 21:54:07 GMT

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

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

AntrikshSharma — Tue, 29 Sep 2020 03:41:07 GMT

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