topic Re: Why do we need COUNTROW in the DAX formula? in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201948#M19251
<P>Hi <LI-USER uid="243570"></LI-USER> ,</P><P> </P><P> </P><P>Have a look at this to understand Count Rows.</P><P> </P><P> </P><P><A href="https://dax.guide/countrows/" target="_blank">https://dax.guide/countrows/</A></P><P><A href="https://curbal.com/blog/glossary/countrows" target="_blank">https://curbal.com/blog/glossary/countrows</A></P><P><A href="https://info.enterprisedna.co/dax-function-guide/countrows/" target="_blank">https://info.enterprisedna.co/dax-function-guide/countrows/</A></P><P> </P><P><BR />Regards,</P><P>Harsh Nathani</P><P><BR /><STRONG>Appreciate with a Kudos!! (Click the Thumbs Up Button)</STRONG></P><P><STRONG>Did I answer your question? Mark my post as a solution!</STRONG></P>Sun, 05 Jul 2020 14:59:31 GMTharshnathani2020-07-05T14:59:31ZWhy do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201167#M19202
<P>Hi all, </P><P> </P><P>I couldn't visualize what is PowerBI doing with the COUNTROW function behind the below DAX formula. If I don't include COUNTROW, the formula would return an error as it was unable to return a scalar value. </P><P> </P><P>My understanding with the formula is, Power BI will return a table with a set of unique customer names, run the filter on whether the customers matches the two criteria, and finally return the total sales for each customer in the table if it matches the criteria. I couldn't visualize what COUNTROW is doing or returning in this case.</P><P> </P><DIV><DIV><SPAN>Outlier Sales = CALCULATE( [Total Sales] , </SPAN></DIV><DIV><SPAN>FILTER( VALUES( Customers[Customer Names] ), </SPAN></DIV><DIV><SPAN>COUNTROWS(</SPAN></DIV><DIV><SPAN>FILTER( 'Outlier Detection Logic' , </SPAN></DIV><DIV><SPAN>[Profit Margin] >= 'Outlier Detection Logic'[Profit Margins Min] &&</SPAN></DIV><DIV><SPAN>[Total Sales] >= 'Outlier Detection Logic'[Profit Margins Min] )) >0 ))</SPAN></DIV></DIV>Sat, 04 Jul 2020 01:46:42 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201167#M19202kimjongbun2020-07-04T01:46:42ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201285#M19206
<P>COUNTROWS is checking if the FITLER inside is returning any row and if COUNTROWS is 0 then that ROW isn't returned by FILTER for the CALCULATE, in simple words, FILTER ( VALUES ( Customer[Customer Name] ) .....) construct iterates for each customer and checks wheter the count of rows returned by the inner FILTER is greater than 0 and if it is greater than 0 then keep that customer otherwise remove it from the list once everything is computed outer FILTER returns only CustomerNames that have atleast 1 row in Outlier table and then CALCULATE pushes this list into the filter context and then evaluates [Total Sales]</P>Sat, 04 Jul 2020 09:28:46 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201285#M19206AntrikshSharma2020-07-04T09:28:46ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201884#M19234
<P>Hi <LI-USER uid="236628"></LI-USER> <SPAN> , </SPAN></P><P> </P><P><SPAN>Thank you so much for your clear explanation. Could you enlighten me why the formula is unable to return a scalar value if I don't include COUNTROW in the formula? Why would there be multiple columns? </SPAN></P>Sun, 05 Jul 2020 12:49:05 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201884#M19234kimjongbun2020-07-05T12:49:05ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201899#M19236
<P>Because when you remove COUNTROWS you are removing aggregation and the second part of FILTER is for specifying conditions/boolean operations because of which FILTER is able reduce the number of rows returned by it. Also, I just realised that you should insert CALCULATE before COUNTROWS so that the Outliers related to the customers can be counted</P><LI-CODE lang="markup">Outlier Sales =
CALCULATE (
[Total Sales],
FILTER (
VALUES ( Customers[Customer Names] ),
CALCULATE (
COUNTROWS ( 'Outlier Detection Logic' ),
FILTER (
'Outlier Detection Logic',
[Profit Margin] >= 'Outlier Detection Logic'[Profit Margins Min]
&& [Total Sales] >= 'Outlier Detection Logic'[Profit Margins Min]
)
) > 0
)
)</LI-CODE>Sun, 05 Jul 2020 13:38:58 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201899#M19236AntrikshSharma2020-07-05T13:38:58ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201948#M19251
<P>Hi <LI-USER uid="243570"></LI-USER> ,</P><P> </P><P> </P><P>Have a look at this to understand Count Rows.</P><P> </P><P> </P><P><A href="https://dax.guide/countrows/" target="_blank">https://dax.guide/countrows/</A></P><P><A href="https://curbal.com/blog/glossary/countrows" target="_blank">https://curbal.com/blog/glossary/countrows</A></P><P><A href="https://info.enterprisedna.co/dax-function-guide/countrows/" target="_blank">https://info.enterprisedna.co/dax-function-guide/countrows/</A></P><P> </P><P><BR />Regards,</P><P>Harsh Nathani</P><P><BR /><STRONG>Appreciate with a Kudos!! (Click the Thumbs Up Button)</STRONG></P><P><STRONG>Did I answer your question? Mark my post as a solution!</STRONG></P>Sun, 05 Jul 2020 14:59:31 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1201948#M19251harshnathani2020-07-05T14:59:31ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1202350#M19271
<P>Hi <LI-USER uid="236628"></LI-USER> ,</P><P> </P><P>Am I correct to say that DAX is using the following sequence to get to the solution? Bold and Italic are the booleans / measure, and the number in the blue circle is the sequence in which DAX is following.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="kimjongbun_0-1594001486497.png" style="width: 885px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/305526iF1CCAB844BC754FF/image-dimensions/885x332?v=1.0" width="885" height="332" role="button" title="kimjongbun_0-1594001486497.png" alt="kimjongbun_0-1594001486497.png" /></span></P><P> </P>Mon, 06 Jul 2020 02:12:14 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1202350#M19271kimjongbun2020-07-06T02:12:14ZRe: Why do we need COUNTROW in the DAX formula?
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1202574#M19274
<P>Yes, that's correct.</P>Mon, 06 Jul 2020 06:11:32 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-do-we-need-COUNTROW-in-the-DAX-formula/m-p/1202574#M19274AntrikshSharma2020-07-06T06:11:32Z