Count rows in table visual with Field Parameters

[tannhq]

Hello everyone, 

I have a question.
Before that, I can use the COUNTROWS and SUMMARIZECOLUMNS to caculate the number of rows visible in the visual table.
But now we apply Field parameters, how can we know.

Code to caculate rows before.
No of Raw Table =
COUNTROWS (
SUMMARIZECOLUMNS (
'Product'[Franchise],
'Product'[STMS Product Group],
'Product'[STMS Product Name],
'CustomerType'[CHANNEL (groups)],
'Calendar'[Year-Month],
'Region'[REGION],
'Area'[AreaName],
'Customer'[CustomerName],
'Sales'[InvoiceDate],
'Customer'[Code],
"Value", 'Measure'[Value]
)
)
Thanks,

[Ashvini3Jadhav]

Hi @tannhq, @gp10 , @algunn14 ,

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.

[lbendlin]

use as is. Don't specify a table.

[ppdas2112]

@lbendlin something has to be specified? I am getting the above error.

Basically I would need the names of the fields that I have selected using Field Parameter inside ALLSELECTED(). How can I make that dynamic? That will solve the problem

[lbendlin]

Please provide some sample data.  Are you using a matrix visual?

[ppdas2112]

https://data.ok.gov/dataset/dd1ecf41-4abc-4886-ab0f-b84d7662d8d4/resource/6fe771bd-a705-4fce-b12f-d0... 

Hi @lbendlin , I am using a Table visual. Take the above data as example. If we just select FISCAL_YEAR in the Fields Parameter slicer, the number of rows in the Table will be much lower compared to if we take both FISCAL_YEAR and ACCOUNTING_PERIOD

[lbendlin]

Here is the query for both parameters

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'ledger_fy22_qtr1'[FISCAL_YEAR],
			'ledger_fy22_qtr1'[ACCOUNTING_PERIOD],
			"SumPOSTED_TOTAL_AMT", CALCULATE(SUM('ledger_fy22_qtr1'[POSTED_TOTAL_AMT]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0Core,
			'ledger_fy22_qtr1'[FISCAL_YEAR],
			1,
			'ledger_fy22_qtr1'[ACCOUNTING_PERIOD],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'ledger_fy22_qtr1'[FISCAL_YEAR], 'ledger_fy22_qtr1'[ACCOUNTING_PERIOD]

and here for only fiscal period selected

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"'ledger_fy22_qtr1'[FISCAL_YEAR]"}, 'Parameter'[Parameter Fields])

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'ledger_fy22_qtr1'[FISCAL_YEAR],
			__DS0FilterTable,
			"SumPOSTED_TOTAL_AMT", CALCULATE(SUM('ledger_fy22_qtr1'[POSTED_TOTAL_AMT]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'ledger_fy22_qtr1'[FISCAL_YEAR], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'ledger_fy22_qtr1'[FISCAL_YEAR]


// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"'ledger_fy22_qtr1'[FISCAL_YEAR]"}, 'Parameter'[Parameter Fields])

	VAR __DS0Core = 
		CALCULATETABLE(
			SUMMARIZE(
				'Parameter',
				'Parameter'[Parameter Fields],
				'Parameter'[Parameter Order],
				'Parameter'[Parameter]
			),
			KEEPFILTERS(__DS0FilterTable)
		)

	VAR __DS0BodyLimited = 
		TOPN(
			152,
			__DS0Core,
			'Parameter'[Parameter Order],
			1,
			'Parameter'[Parameter Fields],
			1,
			'Parameter'[Parameter],
			1
		)

EVALUATE
	__DS0BodyLimited

ORDER BY
	'Parameter'[Parameter Order], 'Parameter'[Parameter Fields], 'Parameter'[Parameter]

That means you can use concatenatex(parameter,[Parameter],",")  or similar to figure out which field parameters are currently selected. From there you can calculate the count of "rows" in the table visual.

[ppdas2112]

@lbendlin would you kindly create a measure for me, since I am still pretty much confused on how to do that! 🙄

[lbendlin]

Rowcount = 
SWITCH(concatenatex(parameter,[Parameter],","),
"ACCOUNTING_PERIOD",COUNTROWS(SUMMARIZE(ALLSELECTED('ledger_fy22_qtr1'),[ACCOUNTING_PERIOD])),
"FISCAL_YEAR",COUNTROWS(SUMMARIZE(ALLSELECTED('ledger_fy22_qtr1'),[FISCAL_YEAR])),
COUNTROWS(SUMMARIZE(ALLSELECTED('ledger_fy22_qtr1'),[FISCAL_YEAR],[ACCOUNTING_PERIOD]))
)

[algunn14]

This is as close as I've seen anyone get to the solution I'm looking for. In my situation, the user wants to dynamically add multiple fields from one table, but their are over 100 fields in this table and they could add any combination of fields. I'm trying to think of a way to modify your dax above to account for the endless possible variations of column selections. 

I'm stumped right now, if you had any thoughts I would love to hear them! Thanks.

[lbendlin]

It's all manual, and unmanageable for more than 5 parameters.  You are looking at 2 to the power of parameter count permutations.

[gp10]

Hi @lbendlin ,
if we have 2 field parameters on our table visual how would that measure look?
And if the Field Parameter has columns from different tables?

Imagine a table visual with field parameter like below:

Measures = {

    ("Total Sales", NAMEOF('Measures Table'[Total Sales]), 0),

    ("Avg Sales", NAMEOF('Measures Table'[Average Sales]), 1),

}

and

Categories = {

    ("Product", NAMEOF('Product Table'[Product]), 0),

    ("Country", NAMEOF('Country Table'[Country]), 1),

    ("Store", NAMEOF('Stores Table'[Store]), 2)

}

Thanks.

[lbendlin]

Same methodology. Instead of permutations for two columns your SWITCH statement needs to handle permutations over three columns.

The measures field parameters are irrelevant as they don't contribute cardinality.

[gp10]

True, measures are irrelevant.
The thing is the different tables, not the number of columns.
So for example if I have columns from 4 different tables what would I write here?

COUNTROWS(SUMMARIZE(ALLSELECTED('ledger_fy22_qtr1'),[FISCAL_YEAR],[ACCOUNTING_PERIOD]))

[gp10]

@lbendlinthe problem is when columns from 2 or more different tables are selected in the Field Parameter.

[lbendlin]

use the same query as the visual uses when you combine these columns from different tables. SUMMARIZECOLUMNS etc.  

[ppdas2112]

Thank you @lbendlin , you're a lifesaver!

[ppdas2112]

Hi @tannhq @lbendlin is there a solution to this problem, if yes, kindly help!

[lbendlin]

COUNTROWS(ALLSELECTED()) should work.

[ppdas2112]

@lbendlin what should I put inside ALLSELECTED()? If I put the table name, the original table row count is shown, not the dynamic row count based on Field Parameter selection. Pls note that the row count keeps changing based on selection of Fields

[lbendlin]

As long as you use a measure to count your rows everything will keep working as before.  If you use a calculated column then that will stop working.