Forum Discussion
Count rows in table visual with Field Parameters
- 4 years ago
It's a two step process. Check the article at sqlbi.com . Fields parameters in Power BI - SQLBI
Unfortunately it is not (yet) possible to provide lists of columns instead of individual columns
Please provide some sample data. Are you using a matrix visual?
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
- lbendlin3 years agoSuper User
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.
- ppdas21123 years agoHelper I
lbendlin would you kindly create a measure for me, since I am still pretty much confused on how to do that! 🙄
- lbendlin3 years agoSuper User
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])) )