Dynamic Summarise using Column Name instead of Column Reference?

[Anonymous]

I am wondering if a method exists to summarize values dynamically by using the string value of a column name instead of a column reference.

I have around 38 dimension variables that can be grouped together on one of our internal tools by up to 3 different groups being applied together. Hence the total combinations of different groups equate to approx 50k permetations of different grouping combinations. Within each group contains approx 2 to 100 different possible values. For example. One group maybe 'Gender' with 'Male' and 'Female' as the possible values. Another group maybe 'Age Bracket' with values in bins of 5 year increments. 

I am trying to achieve a way to calculate the optimised groupings by taking a flat fact table that contains a column for each dimension variable, and a corresponding value that I want to sum based on each group combination. 

I can achieve this by unpivoting this data in power query, however per 'campaign' activity it generates around 125 million records. I have around 50 active campagins at any one stage, therefore generating approx 6.25 billion rows of data. (This does not load to my data model very successfully) - Therefore trying to work out a way with the flat fact table to calculate this on the fly in DAX.

With the SUMMARIZE function it requires the column reference in the group by variable. This obviously with 50k combinations becomes quite an unmanageable formula to write. Is there any function or approx that allows a DAX function to have an input of a column name by a string to group instead? Or another workable approach that may work. 

For example I want to be able to write something like this .... SUMMARIZE(factTable, "Gender", "Age Bracket", "Result", SUM(factTable[FactColumn)) is my ideal, so I can use another table to feed in the column names for each group combination.

Instead of the current acceptable formula of  SUMMARIZE(factTable, factTable[Gender], factTable[Age Bracket], "Result", SUM(factTable[FactColumn))

Any clues on an approach that might work for such a problem. 

[TeigeGao]

Hi @Anonymous ,

Based on my searching, I haven't found a method to do this, according to your description, my understanding is that you have 50k combinations of groups required doing summarize.

But why will you use "Gender", "Age Bracket" rather than factTable[Gender], factTable[Age Bracket], I think they have no differnce when you write them, for "Gender", "Age Bracket" you also need to write 50k times, actually, we can generate the DAX query in some RDBMS by Concatenation string.

Best Regards,

Teige

[Anonymous]

Thanks for your response Teige.

The reason I would prefer the string versions, rather than the column references is that I have a table with all the combinations of groups. 

Group 1 | Group 2 | Group 3 | GroupCominationIndexID

Gender  | null         | null         | 1

Age       | null          | null        | 2

Gender  | Age         | null        | 3

This table holds around 50k records for every combination.

If I feed the 'Summarize' with the string value of the selected value of the GroupCombinations table then I will be able to generate my answer on the fly by referencing this table to group my fact table.

Let me know if you need further clarification if my explaination doesn't make complete sense.

Regards,

Tim.

[Anonymous]

@TeigeGao - What do you mean by this comment "we can generate the DAX query in some RDBMS by Concatenation string."