Forum Discussion
Count rows in table visual with Field Parameters
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
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
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.
lbendlin would you kindly create a measure for me, since I am still pretty much confused on how to do that! 🙄
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])) )