Number of rows in Power BI when both tables have the same creation date and the same person

[summer123]

I have two tables. One contains information about the verification of the client's data, and the other contains information about the activities carried out by the consultant. I want to calculate the number of rows in Power BI when both tables have the same creation date and the same person who created the verification and activity. Please help.

[lbendlin · Accepted solution]

let
    Source = Table.NestedJoin(#"First Table", {"Verification Date", "Verification person"}, #"Second Table", {"Date", "Consultant"}, "Second Table", JoinKind.Inner),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Verification Date", "Verification person"})
in
    #"Removed Other Columns"

[Anonymous · Accepted solution]

Hi @summer123 ,

First of all thanks to lbendlin  for the quick reply and solution. Allow me to offer another insight:
You can also create a calculated table if you don't want to add a table to the Power query.

Result Table = 
SELECTCOLUMNS (
    FILTER (
        'First table',
        CONTAINSROW (
            SUMMARIZE ( 'Second table', 'Second table'[Consultant], 'Second table'[Date] ),
            'First table'[Verification person],
            'First table'[Verification Date]
        )
    ),
    [Verification person],
    [Verification Date]
)

We can then create a measure to count the number of rows.

Countrows = COUNTROWS(ALL('Result Table'))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[summer123]

First table:

ClientID	Name	Surname	Position	e-mail	Verification Date	Verification person
1213	Kate	Johnson	accountant	Kate@dot.com	01.12.2024	Amelia
456	Ann	Novak	specialist	Ann@dot.com	03.12.2024	Emma
789	Fiona	Smith	director	Fiona@dot.com	01.12.2024	Ava
900	Mark	Ross	specialist	Mark@dot.com	05.12.2024	Mia

Second table

Consultant	Activity	Date
Ava	call	01.12.2024
Amelia	call	01.12.2024
Sophia	call	01.12.2024
Isa	call	01.12.2024

Result I want:

Verification person	Verification Date
Ava	01.12.2024
Amelia	01.12.2024

[lbendlin · Accepted solution]

let
    Source = Table.NestedJoin(#"First Table", {"Verification Date", "Verification person"}, #"Second Table", {"Date", "Consultant"}, "Second Table", JoinKind.Inner),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Verification Date", "Verification person"})
in
    #"Removed Other Columns"

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...