Return the number of rows displayed in a visual using a field parameter

[Jennifer_L_Mann]

Hello all, 

I need to display a total count for the number of rows in a grid after it has been filtered. A few caveats to this which is why I'm having trouble: 

1. Our table is using a field parameter so users can select which columns they want to include/exclude. Due to this the only "column" on the grid visual is our "Select Columns" parameter. 
2. There are page filters but due to #1 we have also trained users to select the grid and apply filters at the "Filters on this visual" level as well

I can get a count to work but it only works for the number of rows filtered at the page level, if a users filters at the "filters on this visual" level the count does not update. 

Is there a way to get around this? 

I have tried the solution referenced here (How to return the number of rows displayed in a visual) but it runs into the same issue with #2. 

Thanks in advance, 

Jenn

[danextian · Accepted solution]

Hi @Jennifer_L_Mann 

If you want  the count of rows as it appears in the visual, you must create a mesure that virtually mimics the table and count its rows. Since you are using a field parameter, you must take into consideration as well the visible columns which gets complicated if there are multiple columns that can selected at the same time as you must consider all the possible combinations of these columns. You can't just count the rows of a table as that will not always match the rows in a visual.

Sample DAX:

Visual Row Count = 
VAR _SelectedOrders = VALUES(DateFieldParam[DateFieldParam Order])

-- Single-column scenarios
VAR _Year = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            "@value", [Total Revenue]
        )
    )

VAR _Month = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month Short],
            "@value", [Total Revenue]
        )
    )

VAR _MonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Two-column combinations
VAR _YearMonth = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month Short],
            "@value", [Total Revenue]
        )
    )

VAR _YearMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

VAR _MonthMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month Short],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Three-column combination
VAR _YearMonthMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month Short],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Select scenario based on selected field parameter(s)
VAR _Result =
    SWITCH(
        TRUE(),
        -- Single column selections
        0 IN _SelectedOrders && NOT 1 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _Year,
        1 IN _SelectedOrders && NOT 0 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _Month,
        2 IN _SelectedOrders && NOT 0 IN _SelectedOrders && NOT 1 IN _SelectedOrders, _MonthYear,

        -- Two-column combinations
        0 IN _SelectedOrders && 1 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _YearMonth,
        0 IN _SelectedOrders && 2 IN _SelectedOrders && NOT 1 IN _SelectedOrders, _YearMonthYear,
        1 IN _SelectedOrders && 2 IN _SelectedOrders && NOT 0 IN _SelectedOrders, _MonthMonthYear,

        -- All three columns selected
        0 IN _SelectedOrders && 1 IN _SelectedOrders && 2 IN _SelectedOrders, _YearMonthMonthYear,

        -- Fallback (no selection)
        _YearMonthMonthYear
    )

RETURN
    _Result

Please see the attached pbix.

[v-ssriganesh]

Hello @Jennifer_L_Mann,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hi @Jennifer_L_Mann,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @danextian, @cengizhanarslan & @krishnakanth240 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[krishnakanth240 · Accepted solution]

Hi @Jennifer_L_Mann 

Force filtering via slicers

If you want the row count to always match
You must push filters into
Page filters
Report filters
Slicers
Visible Row Count :=
COUNTROWS (
VALUES ( 'FactTable'[PrimaryKey] )
)

Best practice - Disable “Filters on this visual” and guide the users to use slicers

[danextian · Accepted solution]

Hi @Jennifer_L_Mann 

If you want  the count of rows as it appears in the visual, you must create a mesure that virtually mimics the table and count its rows. Since you are using a field parameter, you must take into consideration as well the visible columns which gets complicated if there are multiple columns that can selected at the same time as you must consider all the possible combinations of these columns. You can't just count the rows of a table as that will not always match the rows in a visual.

Sample DAX:

Visual Row Count = 
VAR _SelectedOrders = VALUES(DateFieldParam[DateFieldParam Order])

-- Single-column scenarios
VAR _Year = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            "@value", [Total Revenue]
        )
    )

VAR _Month = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month Short],
            "@value", [Total Revenue]
        )
    )

VAR _MonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Two-column combinations
VAR _YearMonth = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month Short],
            "@value", [Total Revenue]
        )
    )

VAR _YearMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

VAR _MonthMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Month Short],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Three-column combination
VAR _YearMonthMonthYear = 
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Category[Category],
            Dates[Year],
            Dates[Month Short],
            Dates[Month and Year],
            "@value", [Total Revenue]
        )
    )

-- Select scenario based on selected field parameter(s)
VAR _Result =
    SWITCH(
        TRUE(),
        -- Single column selections
        0 IN _SelectedOrders && NOT 1 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _Year,
        1 IN _SelectedOrders && NOT 0 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _Month,
        2 IN _SelectedOrders && NOT 0 IN _SelectedOrders && NOT 1 IN _SelectedOrders, _MonthYear,

        -- Two-column combinations
        0 IN _SelectedOrders && 1 IN _SelectedOrders && NOT 2 IN _SelectedOrders, _YearMonth,
        0 IN _SelectedOrders && 2 IN _SelectedOrders && NOT 1 IN _SelectedOrders, _YearMonthYear,
        1 IN _SelectedOrders && 2 IN _SelectedOrders && NOT 0 IN _SelectedOrders, _MonthMonthYear,

        -- All three columns selected
        0 IN _SelectedOrders && 1 IN _SelectedOrders && 2 IN _SelectedOrders, _YearMonthMonthYear,

        -- Fallback (no selection)
        _YearMonthMonthYear
    )

RETURN
    _Result

Please see the attached pbix.

[Jennifer_L_Mann]

Thanks for sharing! Unfortunately, we have ~170 columns for them to choose from so I this solution wont work for us, but definitely something to keep in mind in the future for smaller projects. 

[danextian]

You can ask AI to generate the code for you. You input the parameter table formula and ask to generate a formula similar to the one i gave you but using your field parameters. But with 170 and if a user can select more than one columns there's just too many combinations. AI says it is around 1.49 quindecillion. 

[cengizhanarslan]

When you place the count outside the table visual (e.g., a Card), it only evaluates in the page/report filter context + slicers — not the table visual’s internal filters. There’s no supported DAX function to read “Filters on this visual” from another visual.

So the only reliable options are:

1) Put the count inside the same table visual
Add a measure like:

Row Count (in this visual) =
COUNTROWS ( VALUES ( Fact[YourRowKey] ) )

Because it’s evaluated inside the same visual, it respects the visual-level filters.

2) Move those “Filters on this visual” into something global
Use slicers / page filters / a filter panel (synced slicers). Then your card measure will update correctly.

3) If the table is truly driven by field parameters only
Make sure you still have a stable row identifier in the visual (even hidden), e.g. Fact[ID] in the rows, and count that. Field parameters only control displayed fields; they shouldn’t be the only thing defining the grain.

[Jennifer_L_Mann]

Thank you for your quick response! 

I will give #1 a try. As for #2, there are ~170 columns for them to choose from, which is why we want them to filter on the visual and not put all those filters on the page. 

For 3#, I'm not sure I'm following. There are number of unique ID fields that they can choose from the column selector, but counting those fields would still have to be done on the visual like you mentioned in #1, yes? The only field we have included on our table is the "select columns" parameter field.