Dynamic COUNTROWS Measure Across All Tables

Question

I have 6 tables and am creating 6 separate sheets of visuals per table. I want to have a measure that just shows a simple row count aggregate. For naming consistency, I want to create one measure named 'OLs' (order lines) that dynamically switches out based on the table selected (via visual tab selection or something similar).

I have been manually creating a measure in each table, but PBI doesn't allow different measures with the same name. The requirement is to have one consistent name.

I guess the other option is to create a column in each table's query using M, but I've heard that this method isn't recommended for aggregation.

OLs = COUNTROWS(Table1)

Pavel Klammert Pavel Klammert · Accepted Answer · 2019-07-09T13:32:17

assuming you have the real calculations already created, here's what you should do.

Create new table with one column, which has the possible selections in it.

Then create new measure with desired name, which will do the logic.

measure_name = Switch (
     True(), 
     SelectedValue(customTable[custom column] = "selection 1"), Metric A,
     SelectedValue(customTable[custom column] = "selection 2"), Metric B,
     ...,
     Blank()

)

After having a working measure, you put a slicer of the created selection column and force the single select. Users will be able to select desired calculation based on the slicer selection.

Workaround: create table union in M and then have just a single calculation.

Dynamic COUNTROWS Measure Across All Tables

1 Answers