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 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Just wow :O... My affected_by Column code by nandic  hasnt finished running yet will update and try this once it is done...

Again just wow and thanks..You guys are legends