cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate columns in a new table based on multiple tables

Hello everyone,

I have a problem and don't know how to proceed.

I have several tables. For each table (table 1 and 2) I would like to count the number of distinct values in column "Value":

Table1 --> Number of distinct values = 2

 ID Value 1 xyz 5 xyz 12 abc

Table 2 --> Number of distinct values = 3

 ID Value 2 abc 4 def 10 ghi

Based on this I would like to create a new table containing a list of the number of the distinct values for each table:

New table

 Table Number of distinct values Table 1 2 Table 2 3

I hope someone can help. Thanks in advance!

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous

Try this code to create a new table:

``````Count Table =
UNION (
SUMMARIZE (
'Table 1',
"Table", "Table 1",
"Number of distinct values", DISTINCTCOUNT ( 'Table 1'[Value] )
),
SUMMARIZE (
'Table 2',
"Table", "Table 2",
"Number of distinct values", DISTINCTCOUNT ( 'Table 2'[Value] )
)
)``````

Output:

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

Appreciate your Kudos  !!

Super User

Hi @Anonymous

Try this code to create a new table:

``````Count Table =
UNION (
SUMMARIZE (
'Table 1',
"Table", "Table 1",
"Number of distinct values", DISTINCTCOUNT ( 'Table 1'[Value] )
),
SUMMARIZE (
'Table 2',
"Table", "Table 2",
"Number of distinct values", DISTINCTCOUNT ( 'Table 2'[Value] )
)
)``````

Output:

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

Appreciate your Kudos  !!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors