Power BI中跨多表创建统一名称动态COUNTROWS度量值OLs的需求

Got it, let's tackle this Power BI problem you're facing—having a single reusable OLs measure that counts rows dynamically across your 6 tables, without running into duplicate measure name issues. Here are a couple of solid, practical solutions:

Option 1: Use a Parameter + SWITCH DAX Measure #

This is the most straightforward approach, letting you tie the measure to a selectable parameter that you can set per page.

Step-by-Step Setup: #

1. Create a Table Selection Parameter

   • Go to the Modeling tab > click New Parameter
   • Choose Text as the data type
   • In the "Values" field, enter the exact names of your 6 tables (e.g., Table1, Table2, ..., Table6)
   • Set a default value (like Table1 for your first page) and click OK

2. Write the Dynamic OLs Measure
   Create a new measure with this DAX code, replacing the table names with your actual ones:

   OLs = 
   SWITCH(
       SELECTEDVALUE('Table Selection'[Table Selection]), // Match your parameter name
       "Table1", COUNTROWS(Table1),
       "Table2", COUNTROWS(Table2),
       "Table3", COUNTROWS(Table3),
       "Table4", COUNTROWS(Table4),
       "Table5", COUNTROWS(Table5),
       "Table6", COUNTROWS(Table6),
       BLANK() // Fallback if no table is selected
   )
   

3. Configure per Page
   For each visualization page, set the parameter's current value to match the table the page is focused on. The OLs measure will automatically calculate the row count for that table.

Option 2: Use Page-Level Filters + Table Identifier Columns #

If each page already uses page-level filters to isolate one table, you can add a static identifier column to each table and use that to drive the measure.

Step-by-Step Setup: #

1. Add a "Table Name" Column to Each Table

   • Open the Query Editor for each table
   • Add a custom column using M code (replace Table1 with the actual table name):

     = Table.AddColumn(PreviousStepName, "Table Name", each "Table1")
     

   • Close and apply changes to load these columns into your model.

2. Write the OLs Measure
   This measure reads the selected table name from your page-level filter and returns the corresponding row count:

   OLs = 
   VAR CurrentTable = SELECTEDVALUE(Table1[Table Name]) // Any table works here—page filter will narrow it down
   RETURN
   SWITCH(
       CurrentTable,
       "Table1", COUNTROWS(Table1),
       "Table2", COUNTROWS(Table2),
       // Repeat for all 6 tables
       BLANK()
   )
   

3. Set Page-Level Filters
   On each page, add a page-level filter for the "Table Name" column, selecting the name of the table that page visualizes. The OLs measure will now dynamically count rows for that table.

Why Your Previous Approaches Didn't Work: #

• Duplicate Measure Names: Power BI enforces unique measure names at the model level, so you can't have multiple OLs measures across different tables.
• M Language Columns: Adding a column with M is row-level, so using it for aggregation (like counting rows) would require extra steps and could lead to incorrect counts if your table has duplicate values—this isn't designed for table-wide row count aggregation.

内容的提问来源于stack exchange，提问作者BossRoyce