Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
saikrish_01
Frequent Visitor

Transforming column to multiple rows dynamically.

Hi

 

I have a doubt regarding Dax in power bi. 

 

Lets say I have a column called values which contains 10 values.

 

 

Values
2
4
6
8
6
4
2
6
8
4

 

Let's take a variable called subgroup which is entered by user. for example, subgroup = 2. I need all the value in the values column to get grouped by 2 ( subgroup value) and displayed

 

Row NumberValuesValues
124
268
364
426
584

 

the above example is for subgroup =2

 

lets say we have subgroup = 3

 

Row NumberValuesValuesValues
1246
2864
3268
44NullNull

 

Since, we have only 10 values. the last two fields should be 0 when the subgroup is 3.

 

Is it possible to write a dax measure to perform the above operation?

 

Thanks.

2 REPLIES 2
MAwwad
Solution Sage
Solution Sage

 

It is possible to create a DAX measure that groups values in a column based on a user-entered subgroup value.

Here's an example of a DAX measure that would accomplish this:

 

Copy code
Grouped Values:= VAR SubGroup = SELECTEDVALUE(SubGroup[SubGroup]) VAR CurrentRow = ROWNUMBER() RETURN SWITCH( MOD(CurrentRow,SubGroup), 0, BLANK(), 1, SUMX(FILTER(ALL('Values'),MOD(ROWNUMBER(ALL('Values')),SubGroup)=1),'Values'[Values]), 2, SUMX(FILTER(ALL('Values'),MOD(ROWNUMBER(ALL('Values')),SubGroup)=2),'Values'[Values]), 3, SUMX(FILTER(ALL('Values'),MOD(ROWNUMBER(ALL('Values')),SubGroup)=3),'Values'[Values]), /* add more lines if you want to group more than 3 values*/ ) 

Thanks for the help.

 

I think the doubt is misunderstood. let me try to put it in the correct way.

 

Lets have a Table called Values with random numbers.

 

Values
2
4
6
8
6
4
2
6
8
4

 

I'm having another table called SubGroup.

 

Subgroup Size
2
3
4
5
6
7

 

Now, this column Subgroup size from Subgroup table is added as a slicer. User selects the subgroup size from which I want to create a dynamic table.

 

For Example, if the user selects Subgroup Size as '2'.

A new table should be created with three columns (Row number, value 1 and value 2)

Output :

 

Row NumberValue 1Value 2
124
268
364
426
584

 

Lets take another example, 

if the user selects Subgroup Size as '3'.

A new table should be created with four columns (Row number, value 1,value 2 and value 3).

Output :

 

Row NumberValue 1Value 2Value 3
1246
2864
3268
44NullNull

 

I hope it is clear from the above 2 examples.

Is it possible to write a Dax formula to create a dynamic table like this?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.