Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a new table from existing table (Using DAX Commands)

Hello my saviours, Hope you are all good. I need to create a new table based on calculation from Table 1 My table looks like this  Table 1: IDNO Store_Number Slow  AA1 S1 y AA1 S2 n...
Need Help
Tips and Tricks
Tutorial Requests
  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    It can actually be done in a far, far easier way if you have a column that specifies order in Table1 (or add an index as suggested by nandic). In my previous take, I somehow (must have been asleep) assumed you wanted a solution that would use the "Affected by" column in "Table1 (updated)". This only overcomplicates things unnecessarily

    Table2 =
GENERATE (
    Table1,
    SELECTCOLUMNS (
        CALCULATETABLE (
            DISTINCT ( Table1[Store_Number] ),
            Table1[Index] < EARLIER ( Table1[Index] ),
            Table1[Slow] = "y",
            ALLEXCEPT ( Table1, Table1[IDNO] )
        ),
        "Affected by", Table1[Store_Number]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

AlB's avatar
AlB
Community Champion
5 years ago

Hi Anonymous 

This would be faaar easier in PQ but if you want it in DAX, you can create a calulated table. Table1 in the code is actually what you show as Table1 updated:

 

Table1B =
GENERATE (
    SUMMARIZE ( Table1, Table1[IDNO], Table1[Store_Number] ),
    VAR affectedBy_ =
        CALCULATE ( DISTINCT ( Table1[Affected by] ) )
    VAR numItems_ =
        IF (
            LEN ( affectedBy_ ) = 0,
            0,
            LEN ( affectedBy_ ) - LEN ( SUBSTITUTE ( affectedBy_, ",", "" ) ) + 1
        )
    VAR baseT_ =
        GENERATESERIES ( 1, numItems_ )
    VAR resT_ =
        ADDCOLUMNS (
            baseT_,
            "NewColumn",
                VAR itemNum_ = [Value]
                VAR pos1_ =
                    IF (
                        itemNum_ = 1,
                        0,
                        FIND (
                            UNICHAR ( 160 ),
                            SUBSTITUTE ( affectedBy_, ",", UNICHAR ( 160 ), itemNum_ - 1 ),
                            1,
                            0
                        )
                    )
                VAR pos2_ =
                    VAR foundAt_ =
                        FIND (
                            UNICHAR ( 160 ),
                            SUBSTITUTE ( affectedBy_, ",", UNICHAR ( 160 ), itemNum_ ),
                            1,
                            0
                        )
                    RETURN
                        IF ( foundAt_ = 0, LEN ( affectedBy_ ) + 1, foundAt_ )
                VAR extracted_ =
                    MID ( affectedBy_, pos1_ + 1, pos2_ - pos1_ - 1 )
                RETURN
                    extracted_
        )
    RETURN
        SELECTCOLUMNS ( resT_, "NewColumn", [NewColumn] )
)

 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

AlB's avatar
AlB
Community Champion
5 years ago

Anonymous 

And the same in PQ (much simpler). Copy the M code below in an empty query to see the steps.  #"Changed Type" is your Table1, the processing starts at #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQ0VNJRCgYRlUCsFKsDFzMCEnkQSSRRY6hKVFETuKgOUAWShCncEKCEDlAdkpwZsiawXCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDNO = _t, Store_Number = _t, #"Slow " = _t, #"Affected by" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDNO", type text}, {"Store_Number", type text}, {"Slow ", type text}, {"Affected by", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NewColumn", each Text.Split([Affected by],",")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "NewColumn"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Slow ", "Affected by"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([NewColumn] <> ""))
in
    #"Filtered Rows"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers