Dynamic row count on the visual dependent on field parameter values

[Lavishnu]

Hi All,

I have a Table visual on my dashboard, the columns/fields using a field parameter to select columns dynamically in table visual.

The number of rows changes dynamically dependent on user choices, and the user can pick any combination of values in field parameters.

Need a row count in Card Visual. How many rows appear in the table visual dependent on field parameter selection?

[Ashvini3Jadhav]

Hi @Lavishnu @Daniel29195 

I am happy to inform that I could implement the solution for deriving countrows in table visual/Card visual with field parameters. It can be used in following cases -
1. Countrows based on User's selection
2. Field parameter has columns from multiple tables (More than 1)

If my field parameter(Attribute selection list) has 7 columns,
1. Fact - V_fct_view (Column1,Column5,Column6,Column7)
2. V_dim1_view (Column2)
3. V_dim2_view (Column3,Column4)

Fact is joined with both dimensions.

Field Parameter -
Attribute selection list = {
("Column1", NAMEOF(V_fct_view[Column1]), 0),
("Column2", NAMEOF(V_dim1_view[Column2]), 1),
("Column3", NAMEOF(V_dim2_view[Column3]), 2),
("Column4", NAMEOF(V_dim2_view[Column4]), 3),
("Column5", NAMEOF(V_fct_view[Column5]), 4),
("Column6", NAMEOF(V_fct_view[Column6]), 5),
("Column7", NAMEOF(V_fct_view[Column7]), 6)}

Formula to get Countrows -
Countrows_Custom =
VAR cr = DISTINCT('Attribute selection list'[Attribute selection list Fields])
var tbl = ADDCOLUMNS(V_fct_view,
"C1", if(NAMEOF(V_fct_view[Column1]) in cr, V_fct_view[Column1]),
"C2", if(NAMEOF(V_dim1_view[Column2]) in cr, RELATED(V_dim1_view[Column2])),
"C3", if(NAMEOF(V_dim2_view[Column3]) in cr, RELATED(V_dim2_view[Column3])),
"C4", if(NAMEOF(V_dim2_view[Column4]) in cr, RELATED(V_dim2_view[Column4])),
"C5", if(NAMEOF(V_fct_view[Column5]) in cr, V_fct_view[Column5]),
"C6", if(NAMEOF(V_fct_view[Column6]) in cr, V_fct_view[Column6]),
"C7", if(NAMEOF(V_fct_view[Column7]) in cr, V_fct_view[Column7])
)
RETURN COUNTROWS( SUMMARIZE(tbl,[C1], [C2], [C3], [C4], [C5], [C6], [C7]))

Note - You need to use Related function with dimension columns. The first parameter of Addcolumns function should be Fact.

Please let me know if you need additional details of the function used in the solution or logic applied to derive the solution.

Requesting @Lavishnu  @Daniel29195  you to review the solution and provide the feedback. 🙂

[Daniel29195]

Amazing work !! Appreciate that you share your solution with the community @Ashvini3Jadhav 

[Daniel29195]

@Lavishnu 

the number of rows in the tbale visual will depend on the field paramter selction. 

example .

if you have a tabe with 1000 customers and a table with 500 products

if you choose customers  to be shown in the visual, the visual will have 1000 rows, however if you select the productname,  the visual will show 500 products.

[Lavishnu]

Hi @Daniel29195,

Yes, depending on selection, however if they picked customers, products, and other column, it shows the overall summary count of how many rows are there in the table visual based on selection.

[Daniel29195]

@Lavishnu 

ok i got you, you are asking about the dax measure to write to get the count 😅

mmm, i think you need to create a measure that is counting the rows for each combination :

example let us say we have in the field paramter ,  customer and product .

so i need 3 measures : 

count when product is selected alone , 

count when customer is selected alone

and count when both are seleced . 

[Lavishnu]

@Daniel29195 

In my scenario i have 20 columns the user can pick max 5 combination of values in field parameters.

Columns: Customers, Products, Category, Units, Levels, InStock etc 
Example: 
If Count when products selected alone
If Counts when products & customer where selected
If counts any columns selected where need summary count which ever values selected

[Anonymous · Accepted solution]

Hi @Lavishnu 

Please try this:

First of all, I create a set of sample:

Home > transform data

If you want to save the original table, right-click the table and click reference:

All select the columns and click the Unpivot Columns.

Apply&Close.

Add a measure:

Measure 3 = COUNTROWS('Table (2)')

The result is as follow:

Best Regards

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

[Daniel29195]

@Lavishnu 

im afraid there is no other way for now that im aware of .

found this link, could be helful 

https://community.fabric.microsoft.com/t5/Desktop/Count-rows-in-table-visual-with-Field-Parameters/t...

If this answers your question  ,  mark it as the solution ✅ so can you can help other people in the community find it easily .

[Lavishnu]

I already tried the above solution. Its works for single value selection. If the users selects mutiples values in field parameter its showing overall count.

In my situation any combination users selects need that summary count.

[Daniel29195]

@Lavishnu 

yes i understood what you want to achieve.

however i dont think this is possible for now . 

 to be able to do such a thing , you need to be able to summarize dynamically base on the selected columns selected from the field paramter, which i dont think is possible for now ( as far as i know ) .