Transforming column to multiple rows dynamically.

[saikrish_01]

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 Number	Values	Values
1	2	4
2	6	8
3	6	4
4	2	6
5	8	4

the above example is for subgroup =2

lets say we have subgroup = 3

Row Number	Values	Values	Values
1	2	4	6
2	8	6	4
3	2	6	8
4	4	Null	Null

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.

[MAwwad]

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*/ ) 

[saikrish_01]

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 Number	Value 1	Value 2
1	2	4
2	6	8
3	6	4
4	2	6
5	8	4

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 Number	Value 1	Value 2	Value 3
1	2	4	6
2	8	6	4
3	2	6	8
4	4	Null	Null

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?