Calculate columns in a new table based on multiple tables

[Anonymous]

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!

[VahidDM · Accepted solution]

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