Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

     

    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*/ ) 
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?