Forum Discussion
Dynamic column generation using DAX
1. Create a separate table for distinct codes:
DistinctCodes = DISTINCT(Training[Code])
2. Create a calculated table to summarize the data dynamically:
PROFSUMMARY =
VAR DistinctCodes = DISTINCT(Training[Code])
RETURN
ADDCOLUMNS(
SUMMARIZE(
Training,
Training[ID],
Training[Prof]
),
"DynamicColumns",
SELECTCOLUMNS(
DistinctCodes,
"Code", [Code],
"TotalHours",
CALCULATE(
SUM(Training[Att Hrs]),
Training[Code] = [Code],
Training[Mode] = "Virtual"
)
)
)
Gaga_Jin There are an array of issues with that formula. First, [Code] will not be recognized 5 lines up from the bottom. The reason is that you are trying to refer to a column that you are adding while you are adding it which won't work. You would need 2 ADDCOLUMNS if you wanted to reference [Code]. Second, you will get an error stating that "Multiple columns cannot be convereted to a scalar" or something along those lines because you are trying to fit 2 columns of data into a single column. Third, even if none of that was a problem ( and it is ), you would still not arrive at the desired table, you would have a column called Code and a column called TotalHours which isn't what is desired.