topic Re: Dynamic column generation using DAX in DAX Commands and Tips

Dynamic column generation using DAX

axiosfabric — Fri, 26 Jul 2024 18:12:11 GMT

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??

Re: Dynamic column generation using DAX

Greg_Deckler — Fri, 26 Jul 2024 18:12:27 GMT

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.

Re: Dynamic column generation using DAX

axiosfabric — Fri, 26 Jul 2024 18:24:07 GMT

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 result

Prof	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

Re: Dynamic column generation using DAX

Gaga_Jin — Sat, 27 Jul 2024 10:16:29 GMT

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"
)
)
)

Re: Dynamic column generation using DAX

Greg_Deckler — Sat, 27 Jul 2024 10:57:26 GMT

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.

Re: Dynamic column generation using DAX

Anonymous — Mon, 29 Jul 2024 03:00:24 GMT

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.

Re: Dynamic column generation using DAX

Anonymous — Mon, 29 Jul 2024 08:21:56 GMT

Hi axiosfabric
Source data

Taking a Matrix Visualization and turning off column/row subtotals

if this solves your problem then please accept the same as your solution.