Forum Discussion

DemoFour's avatar
DemoFour
Continued Contributor
6 years ago

Countrows not working how I expected

Can anyone help me please 

 

I have one report and the data looks like this in one table, I used the following code to get the last column. 

 

Name Check =
VAR NameCheck = Clients[Name]
VAR Age = Clients[Age]
VAR Result =
CALCULATE(
    COUNTROWS( Clients ) ,
    Clients[Name] = NameCheck ,
    Clients[Age] = Age
)
Return
Result
 
And what it did was, do as I asked to do. It counted the times
the name was in the column, compared to the filter within
Calcuate..
 
 
 
 
 
 
Now I went to do this again today in a different report. However the code did not work, it just gave me the following result in Duplicate Name??
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
So to get the correct column result, I had to write
 
 
Duplicates =
VAR ClientName = Client[Client Name]
VAR ClientNumber = Client[Client Number]
VAR Result =
CALCULATE(
    COUNTROWS( Client ),
    FILTER (
    Client , Client[Client Name] = ClientName )
)
RETURN
Result

 

Can anyone point out, why I had to explicitly use the Filter function, instead of the filter in calculate.

 

I am very confused, I am new to power BI and I find it often gives me results I don't expect or understand, when I am using the correct code.

 

Thank you for your help, and stay safe 🙂

 
General Comment
Need Help

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi DemoFour ,

     

    Normally, When you use the CALCULATE without filter in a calculated column, it will calculate only the current row

    It is hard to say why the first formula can output the correct result, but if you want to calculate within all the table and without using Filter function, just add a ‘Table’ in the calculate function, such as following:

     

     

    Column =
VAR x = 'Table'[name]
VAR result =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table', 'Table'[name] = x )
RETURN
Result

     

     

     

    If you have any other questions, please kindly ask here and we will try to resolve it.

     

    BTW, pbix as attached.

     

    Best regards,

     

    • DemoFour's avatar
      DemoFour
      Continued Contributor
      6 years ago

      Morning v-lid-msft 

       

      Thank you for your reply, I went back and reread the CALCULATE chapter in Definitive Guide to DAX and you are right that you need to add in FILTER. 

       

      The examples in the book use ALL when using FILTER to find a specific "brand" in a table, but that is not quite what I am doing!? 

       

      So what you are saying is that the DAX was doing what it was meant to, as it was looking at a row by row basis and gave the answer of 1. Whereas with the FILTER in it look at the columns to find out how many matched the FILTER condition.

      I just find it confusing as, as you say it is hard to see why it worked in one report and not another! But thank you for replying and explaining what was needed. Luckily because of the lockdown I am getting much more time to spend on perfecting my use of DAX as normally when I am at work I am not allowed the time to fully immerse myself into DAX.