Forum Discussion
Dynamic row count on the visual dependent on field parameter values
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.
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. 🙂
Amazing work !! Appreciate that you share your solution with the community Ashvini3Jadhav
