Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
summer123
Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS

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"

View solution in original post

Anonymous
Not applicable

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]
)

vtangjiemsft_0-1734403032910.png

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

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

vtangjiemsft_2-1734403102796.png

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. 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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]
)

vtangjiemsft_0-1734403032910.png

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

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

vtangjiemsft_2-1734403102796.png

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
Regular Visitor

First table:

ClientIDNameSurnamePositione-mailVerification DateVerification person
1213KateJohnsonaccountantKate@dot.com01.12.2024Amelia
456AnnNovakspecialistAnn@dot.com03.12.2024Emma
789FionaSmithdirectorFiona@dot.com01.12.2024Ava
900MarkRossspecialistMark@dot.com05.12.2024

Mia

 

Second table

ConsultantActivityDate
Avacall01.12.2024
Ameliacall01.12.2024
Sophiacall01.12.2024
Isacall01.12.2024

 

Result I want:

 

Verification personVerification Date
Ava01.12.2024
Amelia01.12.2024

 

 

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
Super User
Super User

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...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.