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:

IDNOStore_NumberSlow 
AA1S1y
AA1S2n
AA1S3y
AA1S4y
AA1S5n
AA1S6y

Because of slow offloading at stores other stores get affected and hence a new coulmn needs to be created as below showing the problem causing stores .For example : S4 is affected because of slow offloading at s1 and s3 as both these storesd are marked as "y"
Table 1 (Updated)

IDNOStore_NumberSlow Affected by
AA1S1y 
AA1S2nS1
AA1S3yS1
AA1S4yS1,S3
AA1S5nS1,S3,S4
AA1S6yS1,S3,S4


Now i need to code for Affected by Column to display the stores  and also create a new table (Like below) that records each store affected by in a row such as this 

Table 2:

IDNOStore_NumberAffected by
AA1S2S1
AA1S3S1
AA1S4S1
AA1S4S3
AA1S5S1
AA1S5S3
AA1S5S4
AA1S6S1
AA1S6S3
AA1S6S4

By creating this new table i m trying to establish a one to many relationship from which i can then pull up data for visulaization purpose. Thanks a ton 🙂

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 

     

12 Replies

  • 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

      hey nandic,
      Sorry for not making the question clearly, I need the table 2 part where each faulty stores is stored as a record..For example since Store S4 was affected by S1 and S3 and new table having that in individual lines has to be created like this 

      AA1S4S1
      AA1S4S3


      But thanks a lot for the calculated column code that i desgined using some other logic .Your logic is simple and elegant 

      Thanks again

      • 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

      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

       

  • AlB's avatar
    AlB
    Community Champion
    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 

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Anonymous , from the perspective of Power Query, Table 1 (Updated) and Table 2 is identical but only by different transformations.

    I'm having difficulty understanding what you want.🤔

  • CerdoLindo's avatar
    CerdoLindo
    Regular Visitor
    8 months ago

    A follow up question here:   in PowerBI I have a (complex) DAX query that works great within the DAX query view, and returns a table . 

     

    Now I want to use the (complex) query definition to define a table.... yet it fails. Is there any smart way? 


    Hello Community!  I Am trying to use a DAX query to create a table...   My query works fine in the DAX editor... but I fail to transpose it as a table DAX-definition.  
     
    DAX output is:   
    SERIESACTIVITYTCOSERIES_VALUE
    SpecCostClean Energy48.50
    SpecCostIncrease Recycling6830
    SpecCostConvert PDD84.51.77
     
    I would like to have this as a table....   Any idea how to proceed ?   ğŸ™‚ Thanks & KR, Alfred
     
     
    DAX query view code:
     
     
    // DAX Query

    DEFINE

    COLUMN '__SQDS0VisualCalcs'[Percent of grand total] =   ( BLANK() )

    COLUMN '__SQDS0VisualCalcs'[Percent of grand total 1] = ( BLANK() )




    VAR __SQDS0FilterTable =  TREATAS({"2030"}, 'Q6_i6b_ABAT_sort'[YEAR_STREAM])




    VAR __SQDS0FilterTable2 =  TREATAS({"Ghirardelli Chocolate Company"}, 'Q6_i6b_ABAT_sort'[SITE])




    VAR __SQDS0FilterTable3 = 

    FILTER(

    KEEPFILTERS(VALUES('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE])),

    NOT('Q6_i6b_ABAT_sort'[SCOPE_SBT_GROUP_FLAG_TYPE] IN {"Scope ALL"})

    )




    VAR __ValueFilterDM2 = 

    FILTER(

    KEEPFILTERS(

    SUMMARIZECOLUMNS(

    'Q6_i6b_ABAT_sort'[Index],

    'Q6_i6b_ABAT_sort'[Action name],

    __SQDS0FilterTable,

    __SQDS0FilterTable2,

    __SQDS0FilterTable3,

    "SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),

    "SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),

    "meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],

    "meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]

    )

    ),

    [SumSpecCost_kCHF_tCO2e] >= 0

    )




    VAR __SQDS0Core = 

    SUMMARIZECOLUMNS(

    ROLLUPADDISSUBTOTAL(

    'Q6_i6b_ABAT_sort'[Index], "IsSQDS0GrandTotalRowTotal",

    'Q6_i6b_ABAT_sort'[Action name], "IsDM0Total"

    ),

    __SQDS0FilterTable,

    __SQDS0FilterTable2,

    __SQDS0FilterTable3,

    __ValueFilterDM2,

    "SumSpecCost_kCHF_tCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[SpecCost_kCHF_tCO2e])),

    "SumtCO2e", CALCULATE(SUM('Q6_i6b_ABAT_sort'[tCO2e])),

    "meas_MAX", 'Q6_i6b_ABAT_sort'[meas_MAX],

    "meas_COMPL", 'Q6_i6b_ABAT_sort'[meas_COMPL]

    )




    VAR __SQDS0VisualCalcsInput = 

    SELECTCOLUMNS(

    KEEPFILTERS(

    SELECTCOLUMNS(

    __SQDS0Core,

    "Index", 'Q6_i6b_ABAT_sort'[Index],

    "IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],

    "Action_name", 'Q6_i6b_ABAT_sort'[Action name],

    "IsDM0Total", [IsDM0Total],

    "SumSpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],

    "SumtCO2e", [SumtCO2e],

    "meas_MAX", [meas_MAX],

    "meas_COMPL", [meas_COMPL]

    )

    ),

    "Sum of Index", [Index],

    "Action name", [Action_name],

    "IsSQDS0GrandTotalRowTotal", [IsSQDS0GrandTotalRowTotal],

    "IsDM0Total", [IsDM0Total],

    "SpecCost_kCHF_tCO2e", [SumSpecCost_kCHF_tCO2e],

    "tCO2e", [SumtCO2e],

    "meas_MAX", [meas_MAX],

    "meas_COMPL", [meas_COMPL]

    )




    TABLE '__SQDS0VisualCalcs' = 

    __SQDS0VisualCalcsInput

    WITH VISUAL SHAPE

    AXIS rows

    GROUP [Sum of Index] TOTAL [IsSQDS0GrandTotalRowTotal]

    GROUP [Action name] TOTAL [IsDM0Total]   

    ORDER BY   [Sum of Index] ASC,   [Action name] ASC   DENSIFY "IsDensifiedRow"




    VAR __SQDS0RemoveEmptyDensified = 

    FILTER(

    KEEPFILTERS('__SQDS0VisualCalcs'),

    OR(  NOT('__SQDS0VisualCalcs'[IsDensifiedRow]),    NOT(ISBLANK('__SQDS0VisualCalcs'[Percent of grand total]))    )

    )




    VAR __DS0Core = 

    SELECTCOLUMNS(

    KEEPFILTERS(

    FILTER(

    KEEPFILTERS(__SQDS0RemoveEmptyDensified),

    AND(

    '__SQDS0VisualCalcs'[IsSQDS0GrandTotalRowTotal] = FALSE,

    '__SQDS0VisualCalcs'[IsDM0Total] = FALSE

    )

    )

    ),

    "'__SQDS0VisualCalcs'[Sum of Index]", '__SQDS0VisualCalcs'[Sum of Index],

    "'__SQDS0VisualCalcs'[Action name]", '__SQDS0VisualCalcs'[Action name],

    "'__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e],

    "'__SQDS0VisualCalcs'[tCO2e]", '__SQDS0VisualCalcs'[tCO2e],

    "'__SQDS0VisualCalcs'[Percent of grand total]", '__SQDS0VisualCalcs'[Percent of grand total],

    "'__SQDS0VisualCalcs'[Percent of grand total 1]", '__SQDS0VisualCalcs'[Percent of grand total 1],

    "'__SQDS0VisualCalcs'[meas_MAX]", '__SQDS0VisualCalcs'[meas_MAX],

    "'__SQDS0VisualCalcs'[meas_COMPL]", '__SQDS0VisualCalcs'[meas_COMPL]

    )




    VAR __DS0RemoveContextOnlyColumns = UNION(

    SELECTCOLUMNS(

    KEEPFILTERS(__DS0Core),

    "Series","SpecCost",

    "Action name", '__SQDS0VisualCalcs'[Action name],

    "tCO2e", '__SQDS0VisualCalcs'[tCO2e],

    "SeriesVALUE", '__SQDS0VisualCalcs'[SpecCost_kCHF_tCO2e]

    ), 

    SELECTCOLUMNS(

    KEEPFILTERS(__DS0Core),

    "Series","SpecCost_COMPL",

    "Action name", '__SQDS0VisualCalcs'[Action name],

    "tCO2e", '__SQDS0VisualCalcs'[tCO2e],

    "SeriesVALUE", '__SQDS0VisualCalcs'[meas_COMPL]

    )

    )




    VAR OUTPUT = TOPN(501, __DS0RemoveContextOnlyColumns, 1 )




    EVALUATE

    OUTPUT