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 ...
MAwwad's avatar
MAwwad
Icon for Solution Sage rankSolution 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?