topic Re: Number of rows in Power BI when both tables have the same creation date and the same person in DAX Commands and Tips

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

summer123 — Sat, 14 Dec 2024 22:15:39 GMT

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.

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

lbendlin — Sun, 15 Dec 2024 01:39:25 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

summer123 — Sun, 15 Dec 2024 08:52:25 GMT

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

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

lbendlin — Mon, 16 Dec 2024 14:51:39 GMT

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"

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

Anonymous — Tue, 17 Dec 2024 02:39:11 GMT

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.