Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @Rubenvw,
>>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 @Rubenvw,
>>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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |