Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

IDValue
1xyz
5xyz
12abc

 

Table 2 --> Number of distinct values = 3

IDValue
2abc
4def
10ghi

 

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

TableNumber of distinct values
Table 12
Table 23

 

I hope someone can help. Thanks in advance!

Need Help
  • VahidDM's avatar
    VahidDM
    5 years ago

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

     

1 Reply

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

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