Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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
User | Count |
---|---|
72 | |
67 | |
38 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |