Dynamically calculate the number of rows in a table

[一棵黄葛树]

Hi,

I have a self analysis table with row fields consisting of two optional field parameters. There are also three slicers outside the table. Depending on different business needs, different items for the two field parameters will be dynamically selected to generate a self analysis table. How can I write a metric value to dynamically calculate the number of rows in this table?

ths!

[FBergamaschi]

You can use COUNTROWS on that table but without seeing data and code I cannot help more than this

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[一棵黄葛树]

Thank you for your attention. The number of row fields in this table is dynamically determined by two parameters generated in the table. When the first parameter table (field table) selects 3 items (such as product name, brand, and color), and the second parameter table (KPI table) selects 1 item (such as Amount), this table will have 4 fields, and then calculate the number of rows that should be available.

[v-menakakota]

Hi  @一棵黄葛树 ,

Thanks for reaching out to the Microsoft fabric community forum. 

It sounds like you're using field parameters to dynamically build a self-analysis table, where the number of row fields changes based on selections from two slicersone for attributes (like product name, brand, color) and one for KPIs (like Amount). That’s a great use of Power BI’s flexibility.

To estimate the number of rows in that table, you could try a DAX measure like this:

DAX

RowCount =
COUNTROWS(
    SUMMARIZE(
        'YourDataTable',
        SELECTCOLUMNS('FieldParameterTable', "Field", [FieldName]),
        SELECTCOLUMNS('KPIParameterTable', "KPI", [KPIName])
    )
)

This is a conceptual starting point it hasn’t been tested, so you’ll want to adjust the table and column names to match your model. The idea is to summarize your data based on the selected fields and KPIs, then count the resulting rows.

If you’re using field parameters, this Microsoft doc helpful:Use report readers to change visuals - Power BI | Microsoft Learn

Learn how to let report readers dynamically change the visuals in a report by using field parameters.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

Best Regards, 
Community Support Team  

[一棵黄葛树]

Thank you for your attention, you did not misunderstand my meaning.
[FieldName] and [KPIName] are dynamic and cannot be determined, which is the most critical issue.
My current method is to use new visual calculation to create a new column to count the number of rows in the current table.
I initially hoped to present it as a single card image, but it seems that it is currently not feasible.

[v-menakakota]

Hi @一棵黄葛树 ,

Thank you for clarifying. Using visual calculations to count rows is a practical workaround, especially with the current field parameter limitations. While visual calculations are limited to the specific visual and can’t be reused in a card, your approach makes sense.

Another option you could consider is creating a separate table visual with the same field parameter selections and then applying a COUNTROWS measure to it. You might overlay a transparent card or use a tooltip to display the row count more cleanly. While not perfect, this can help achieve a similar effect to a dynamic card.

Best Regards, 
Community Support Team  

[一棵黄葛树]

Thank you for your continued attention. I still don't understand how to use Countrows with your Another option. Could you please write an attachment for reference? Thank you.
My understanding is to copy the original self-service analysis table and only display the total rows, background perspective, and move it to the original card image position. Is that correct?

[v-menakakota]

Hi @一棵黄葛树 ,
Thanks for reaching out to the Microsoft fabric community forum.

I have used a two field parameters to filter the required data to my table visual.
Then in the table visual I have set up a new visual Calculation with the formula 
Calculation = Countrows(Rows)

To create a visual calculation click on the epsilon then on visual calculation finally click custom

where you can write the DAX code using countrows.
Based on slicer selection we will get the row count

Attaching the .pbix for reference.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[一棵黄葛树]

Hi  @v-menakakota ,

Thanks for your answer.But from your attachment, I did not see:
1. Two field parameters;
2. The transparent card chart displays the total number of rows.
Three measures cannot be used either.

[v-menakakota]

Hi @一棵黄葛树 ,

Field parameters is just to filter the matrix, I have implemented it with only one but it should work with two field parameters without any issue, try setting up your .PBIX with the visual calculation measure as previously shared.

Thank you.

[v-menakakota]

Hi @一棵黄葛树 ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team

[一棵黄葛树]

Hi @v-menakakota 

Thank you very much to the community for patiently tracking and proposing solutions. That's all for this topic.

[v-menakakota]

Hi @一棵黄葛树 ,

Thank you for the update.