topic Re: Putting a table and column name into a variable in DAX Commands and Tips

Putting a table and column name into a variable

Newcolator — Fri, 16 May 2025 13:35:10 GMT

Hi!

This is doing my head in, maybe because it's not possible. I've got this DAX:

Filter Test =

VAR AvailableValuesList = CALCULATETABLE(VALUES(DIM_GROUP[Channel]), REMOVEFILTERS(DIM_GROUP[Channel]))

VAR AvailableValuesCount = COUNTROWS(AvailableValuesList)

VAR SelectedValuesList = VALUES(DIM_GROUP[Channel])

VAR SelectedValuesCount = COUNTROWS(SelectedValuesList)

RETURN SelectedValuesCount & "/" & AvailableValuesCount

I am returning a string which shows the number of values selected in a slicer over the total number of values available. This works fine.

I need this DAX for many columns, so ideally I don't want to have to update the column reference many times in the expression. I'm trying to use a variable to store the column reference. Like this:

Filter Test =

VAR ColumnReference = DIM_GROUP[Channel]

VAR AvailableValuesList = CALCULATETABLE(VALUES(ColumnReference), REMOVEFILTERS(ColumnReference))

VAR AvailableValuesCount = COUNTROWS(AvailableValuesList)

VAR SelectedValuesList = VALUES(ColumnReference)

VAR SelectedValuesCount = COUNTROWS(SelectedValuesList)

RETURN SelectedValuesCount & "/" & AvailableValuesCount

But it's not happy: A single value for column 'Channel' in table 'DIM_GROUP' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single....

I've tried

VAR ColumnReference = SELECTCOLUMNS('DIM_GROUP', [Channel])

but that doesn't work either.

Is this even possible?

The ideal would be to have the multi-line dax expression held just once and then I can pass column references to it, but solving this first problem would be enough.

Any help? Many thanks.

Re: Putting a table and column name into a variable

smpa01 — Fri, 16 May 2025 15:36:05 GMT

Newcolator

just so I understand, you are hoping for a user defined function with column name as parametrs in dax. Something like

// Function to sum up values in a specified column function sumColumn(data, column) { return data.reduce((sum, row) => sum + (row[column] || 0), 0); } // Example usage const data = [{'colA': 1, 'colB': 2}, {'colA': 3, 'colB': 4}]; const sumColA = sumColumn(data, 'colA'); const sumColB = sumColumn(data, 'colB'); console.log(`Sum of colA: ${sumColA}`); // Output: Sum of colA: 4 console.log(`Sum of colB: ${sumColB}`); // Output: Sum of colB: 6

But I don't think in the current capacity of dax, this is possible. However, there is a buzz about udf coming to dax. Not sure what functionalities it will offer.

Re: Putting a table and column name into a variable

v-sgandrathi — Mon, 19 May 2025 06:37:40 GMT

Hi Newcolator,

Thank you smpa01 for your response.

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

Re: Putting a table and column name into a variable

Newcolator — Mon, 19 May 2025 07:23:12 GMT

Yes, that would be very useful. If it's not possible at the moment that's fine. I will stop trying to make it work! Thanks.