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 

     

nandic's avatar
nandic
Resident Rockstar
5 years ago

Anonymous ,

First table can be updated with new column "Affected by". In Power Query just add index for this table.
After that add this calculated column:

Affected by =
CONCATENATEX (
    SELECTCOLUMNS (
        FILTER (
            Table_Stores,
            Table_Stores[Slow ] = "y"
                && Table_Stores[Index] < EARLIER ( Table_Stores[Index] )
        ),
        "Store", Table_Stores[Store_Number]
    ),
    [Store],
    ","
)

Screenshot below:



I just didn't understand if that is final result or you need also last table for making relationship?

Cheers,
Nemanja

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi nandic,
The affecetd_by column solution u gave is running for 30 mins since my dataset is huge ( in millions) is there a way to not use the filter() that i belive is increasing the running time

thanks again for your time