Count Ifs - M language or Power Query - Power BI

Question

I m try to use something like Excel COUNTIFS in Power BI Power Query environment without any luck.

I have the below example:

Name    Surname Company
John    ff      A
John    nn      A
John    k       A
John    ggg     D
John    kk      D

What i m trying to get is how many times the name appears having in mind the company.

Up to now i have use:

Power Query

Group by but i want to keep Surname without use it as a filter. Keep Surname i get a wrong count.

Dax

Using the below calculated column i get a wrong answer.

CountName =
CALCULATE (
    COUNTA ( TableName[Name] ),
    FILTER (
        TableName,
        TableName[Company] = SELECTEDVALUE ( TableName[Company] )
            && TableName[Name] = SELECTEDVALUE ( TableName[Name] )
    )
)

Any help will appreciated.

Answer 1

In powerquery/M, click-select the Name and Company columns, right click ... Group By... Add aggregation, and set one Operation to All Rows and one Operation to Count Rows, then hit OK

Use the arrows atop the new data column to expand the [x] surname field

Full sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name", "Company"}, {{"data", each _, type table}, {"count", each Table.RowCount(_), Int64.Type}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Surname"}, {"Surname"})
in  #"Expanded data"

Answer 2

Adding the solution in DAX since you tagged both DAX and Power QUery. The issue with your calculated column is because of the SELECTEDVALUE since it might not behave as expected in the context of a calculated column.

Calculated columns are evaluated in a row context, but without a filter context that SELECTEDVALUE requires to function as you might expect.

Your measure should be like below :

CountByNameAndCompany = CALCULATE(
    COUNTROWS(Test),
    ALLEXCEPT(Test, Test[Name], Test[Company])
)