Reference table dynamically in CountRows

[Anonymous]

Hi All!

I would like to countrows from a lot of tables. To count the rows from one table I am able to use the following DAX code:

Countrows(TABLENAME)

But now, i would like to count rows from around 100 tables. I have 1 table with all the names from the other tables in it.

What i would like to achieve is the following: I would like to reference a table, using another column, to count rows from that table). Is it possible to achieve this with DAX?

countrows(<Name Table>)

Name Table              CountRows

A                              countrows(A)

B                              countrows(B)

C                              countrows(C)

D                             ..

E

F

G

D

etc..

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous,

>>Is it possible to achieve this with DAX?

Current dax not support the operation which you mentioned, you can try to use power query custom function.

Sample:

Custom fucntion switch to choose the result table(input column, output table)

let
    Switch= (input) =>
    let
        values = {
        {"A", TableA},
        {"B", TableA},
        {input, Table}
        },
        Result = List.First(List.Select(values, each _{0}=input)){1}
    in
        Result
in
    Switch

Use: Add custom column to invoke the custom function.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(Switch([Name])))

In addition, you can package the static tables to formula, then use switch function to choose the displayed value.

Calculate column:

DynamicCount = 
Switch(
Table[Table Name],
"A",COUNTROWS('TableA'),
"B",COUNTROWS('TableB'),
"C",COUNTROWS('TableC'),
"D",COUNTROWS('TableD'),0)

Regards,

Xiaoxin Sheng