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
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])) )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),}
andCategories = {("Product", NAMEOF('Product Table'[Product]), 0),("Country", NAMEOF('Country Table'[Country]), 1),("Store", NAMEOF('Stores Table'[Store]), 2)}
Thanks.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.
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.It's all manual, and unmanageable for more than 5 parameters. You are looking at 2 to the power of parameter count permutations.
