Create Table where registers are measures from other table

[h162672]

Hello everyone

This is my case: I have a table and I need to know how many records I have by column.

Table Users from Database

cod	Name	Country
1	Hugo	CO
2	Paco
3	Luis	PE

The new Table

cod

	Table	ColumnName	TotalRows
1	Users	id	3
2	Users	Name	3
3	Users	Country	2

Thanks for your help !!!

[Anonymous · Accepted solution]

Hi @h162672 ,

I suggest you to try this code.

Table 2 =
ADDCOLUMNS (
    VALUES ( 'Table'[cod] ),
    "Table Column Name", SWITCH ( [cod], 1, "Users id", 2, "Users Name", 3, "Users Country" ),
    "TotalRows",
        SWITCH (
            [cod],
            1, CALCULATE ( DISTINCTCOUNT ( 'Table'[cod] ), ALL ( 'Table' ) ),
            2, CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), ALL ( 'Table' ) ),
            3,
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[Country] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Country] <> BLANK () )
                )
        )
)

Result is as below.

Best Regards,
Rico Zhou

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

[tamerj1]

Hi @h162672 
The following calculated table shall provide the desired result

Users 1 = 
VAR IDs =
    SELECTCOLUMNS ( 
        { ( 1, "Users" , "id", COUNTROWS ( Users ) - COUNTBLANK ( Users[cod] ) ) },
         "cod", [Value1], "Table", [Value2], "ColumnName", [Value3], "TotalRows", [Value4] 
    )
VAR Names =
    SELECTCOLUMNS ( 
        { ( 2, "Users" , "Name", COUNTROWS ( Users ) - COUNTBLANK ( Users[Name] ) ) },
         "cod", [Value1], "Table", [Value2], "ColumnName", [Value3], "TotalRows", [Value4] 
    )
VAR Countries =
    SELECTCOLUMNS ( 
        { ( 3, "Users" , "Country", COUNTROWS ( Users ) - COUNTBLANK ( Users[Country] ) ) },
         "cod", [Value1], "Table", [Value2], "ColumnName", [Value3], "TotalRows", [Value4] 
    )
RETURN
    UNION ( IDs, Names, Countries )