Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Reference table dynamically in CountRows

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!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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