Forum Discussion

Newcolator's avatar
Newcolator
Helper II
1 year ago
Solved

Putting a table and column name into a variable

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.
"DAX " "DAX help" 'Need help'
DAX
  • smpa01's avatar
    smpa01
    1 year ago

    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.

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    1 year ago

    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.

    • Newcolator's avatar
      Newcolator
      Helper II
      1 year ago

      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.

       

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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!