How to Count Rows in a Power BI Table Based on Dynamically Selected Columns from a Slicer ?

[nihilor]

Hi everyone,

I’m working on a Power BI report where a visualisation table (called "Display") shows data from the datatable `v_RandomName`. The data displayed in this table is based on two factors:

1. Filters applied to the dataset.
2. Columns selected by the user via a slicer (Segment) that determines which columns are visible in the table.

My goal is to write a DAX measure that dynamically counts the number of rows displayed in the "Display" table, taking into account both the applied filters and the user-selected columns from the slicer.

Here’s a more detailed explanation of what I’m trying to achieve:
- The table `v_RandomeName` contains multiple columns.
- The user can select specific columns to display using a slicer (Segment), and the visible rows are based on both these selections and the filters applied.
- I would like to count the number of rows that are shown in the "Display" table, dynamically adapting to the columns the user has chosen.

I’m aware that DAX has some limitations when it comes to dynamically referencing columns based on slicer selections. However, I’m looking for a way to either:
- Create a DAX measure that can count rows based on the columns selected in the slicer.
- Or perhaps use an alternative approach (like Power Query transformations) to achieve this.

Has anyone faced a similar challenge or found a workaround for counting rows in a dynamically updated table with selected columns? Any insights or examples would be greatly appreciated!

Thanks in advance for your help!

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @bhanu_gautam  offered and i want to offer some more information for user to  refet t.

hello @nihilor , based on your descriotion, you want to display the columns dynamically, you can consider to use the field paramater, you can refer to the following sample.

Sample data:

1.Create a field paramater in Modeling

2.Select the field.

3.Then it will display a slicer for the fields.

4.In table viual, put the paramater to the visual.

5.Then create a measure

Measure = COUNTROWS('Table')

put it to a card visual.

6.Then it can work, e.g i put the "A" field to a slicer and select the field in paramater slicer. 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

[bhanu_gautam]

@nihilor , Try using 

Create a DAX Measure to Count Rows:

You can use the COUNTROWS function in DAX to count the number of rows in the filtered table. This measure will dynamically adapt to the filters applied to the dataset.

RowCount = COUNTROWS(v_RandomName)
Create a DAX Measure to Handle Column Selection:

If you want to dynamically count rows based on the columns selected in the slicer, you can use the ISFILTERED function to check if a specific column is being filtered. Combine this with the CALCULATE function to apply the necessary filters.

DynamicRowCount =
CALCULATE(
COUNTROWS(v_RandomName),
// Add conditions for each column that can be selected in the slicer
IF(ISFILTERED(v_RandomName[Column1]), v_RandomName[Column1], TRUE()),
IF(ISFILTERED(v_RandomName

[nihilor]

yo,

Thank you very much for your reply.

I'm using `Countrows` yes

```
DynamicRowCount = COUNTROWS(ALLSELECTED('v_RandomName'))
```
Which works on applied filters.

Here is the output of your proposal:

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

The CALCULATE function expects its filter arguments to be Boolean expressions (TRUE/FALSE) or filter tables. In my formula, each IF returns either the value of the column (for example, “Non”), or TRUE(). This results in an attempt to convert a textual value into a Boolean, which causes the error mentioned.

In our case, with a dynamic selection of many columns via a slicer, combined with filtering, this approach generates errors and is not scalable.

So I'm exploring other solutions, perhaps using Power Query or Python scripts to dynamically manage the selected columns and count the rows accordingly.
But if you have cheaper solutions, I'd love to hear from you.

Thanks again for your help and suggestions!