Countrows not working how I expected

[DemoFour]

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 🙂

[v-lid-msft]

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]

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.