Calculated table that counts number of rows from other tables

[nok]

Hello!
I have three different tables in this structure:
A-Table

Category	Code
AAA	21QW
AAA	34RF
BBB	343GG

B-Table

Category	Code
AAA	21QW
BBB	FR46
CCC	GGD35
CCC	FDFE3

C-Table

Category	Code
TTT	ASE43
SSS	FDGF4
ZZZ	LIO89
TTT	QW12
TTT	DGFDR

I want to create a new table in PBI that counts the number of rows that exist in each table for each "Category". The result of this new table would be something like this:

Table	Category	Amount of rows
A-Table	AAA	2
A-Table	BBB	1
B-Table	AAA	1
B-Table	BBB	1
B-Table	CCC	2
C-Table	TTT	3
C-Table	SSS	1
C-Table	ZZZ	1

I'm having trouble creating a new table like this... how can I do this?

[V-yubandi-msft · Accepted solution]

Hi @nok ,

Thanks for reaching out to the Fabric community. I’ve tested this approach, and it works perfectly for counting rows per category per source table. I achieved this using a combination of SELECTCOLUMNS, UNION, and SUMMARIZE DAX functions.

FYI:

For more clarity, I have attached the PBIX file. Please go through it, it will give you a clear picture.

Thanks for your responses @grazitti_sapna & @speedramps .

Give it a try and let me know if you need any further clarifications. If this solution meets your requirements, consider marking it as the accepted solution to help other community members facing similar challenges.

[grazitti_sapna]

Hi @nok 
1. Create a calculated table using DAX:
Go to Modeling tab → New Table

CategoryCounts =
UNION (
    SELECTCOLUMNS ( 'A-Table', "Table", "A-Table", "Category", 'A-Table'[Category] ),
    SELECTCOLUMNS ( 'B-Table', "Table", "B-Table", "Category", 'B-Table'[Category] ),
    SELECTCOLUMNS ( 'C-Table', "Table", "C-Table", "Category", 'C-Table'[Category] )
)
This creates a unified table with columns: Table and Category.

2. Now summarize this table to count rows:
Create another calculated table using:
CategoryRowCounts =
SUMMARIZE (
    CategoryCounts,
    CategoryCounts[Table],
    CategoryCounts[Category],
    "Amount of rows", COUNTROWS ( FILTER ( CategoryCounts,
        CategoryCounts[Table] = EARLIER(CategoryCounts[Table]) &&
        CategoryCounts[Category] = EARLIER(CategoryCounts[Category])
    ))
)


🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[speedramps]

Try this

Add a "Table name" column to each table A-B-C

Append the tables to new table 

Produce your report

Please click thumbs up and accept the solution.    It works !  😀

Thank you