Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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"
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.
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.
First table:
ClientID | Name | Surname | Position | 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 |
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"
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...
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |