Forum Discussion
tannhq
Helper I
4 years agoCount rows in table visual with Field Parameters
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...
- 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
lbendlin
Super User
3 years agoHere 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.