Forum Discussion
Dynamic column generation using DAX
PROFSUMMARY =
SUMMARIZE (
Hi,
Training,
Training[ID],
Training[Prof],
"DP101", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP101",Training[Mode] = "Virtual" ),
"DP103", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP103",Training[Mode] = "Virtual" ),
"DP102", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP102",Training[Mode] = "Virtual" ),
"DP320", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP320",Training[Mode] = "Virtual" ),
)
How can be this red section generate dynamically based on the available distinct code in Training[Code].
can someone help me to achive this??
6 Replies
axiosfabric Not really understanding this at all. Can you post sample data and expected output?
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Training =
Prof Code hrs User 1 DP101 2 User 2 DP103 2 User 3 DP102 2 User 4 DP320 2 User 1 DP320 2
Expected table resultProf DP101 DP103 DP102 DP320 User 1 2 2 User 2 2 User 3 2 User 4 2
based on this below code I can achive the above format
PROFSUMMARY =
SUMMARIZE (
Training,
Training[ID],
Training[Prof],
"DP101", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP101",Training[Mode] = "Virtual" ),
"DP103", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP103",Training[Mode] = "Virtual" ),
"DP102", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP102",Training[Mode] = "Virtual" ),
"DP320", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP320",Training[Mode] = "Virtual" ),
)
but here I need to mention the "DP101" as hard coded
"DP101", CALCULATE ( SUM ( Training[Att Hrs] ), Training[Code] = "DP101",Training[Mode] = "Virtual" ),
I need to generate this section dynamically based on the the distinct code availble
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.
Hi axiosfabric
Maybe you can try to use the Pivot Column feature in the power query:
Open the Power query, select the code column and click the pivot column in the transform pane:
Then Select Hrs in the Value Column box and select Sum in Aggregaet Value Function box:
Click Close & Apply:
The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi axiosfabric
Source dataTaking a Matrix Visualization and turning off column/row subtotals
if this solves your problem then please accept the same as your solution.
