Forum Discussion

ChoiJunghoon's avatar
ChoiJunghoon
Icon for Helper III rankHelper III
4 years ago
Solved

How to create the new columns in the variable table....

Hello. I have a issue again.   Table1  ModuleID | StatusScore | Sequence | Loction A1 0 1 A A1 0 2 B A1 1 3 C A2 0 5 D A2 0 3 E A2 0 4 F A3 1 1 A ...
DAX
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Okay. Can you try below code now:-

    VAR x0 = 
SUMMARIZE (
    FILTER (
        Table1,
        NOT ( Table1[ModuleID] IN VALUES ( 'Table2(remove)'[ModuleID] ) )
    ),
    Table1[ModuleID],
    "SUM_", SUM ( Table1[StatusScore] ),
    "LastSequence", MAX ( Table1[Sequence] ),
    "location",CALCULATE(max(Table1[Location]),Table1[Sequence] = MAX ( Table1[Sequence] ))
)

    Output:-

     

    Thanks,

    Samarth

     

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
4 years ago

Hi ChoiJunghoon ,

 

You can create a custom column in table with below :-

filter =
VAR result =
    CALCULATE (
        FIRSTNONBLANK ( Table1[ModuleID], 1 ),
        FILTER (
            ALL ( 'Table2(remove)' ),
            'Table1'[ModuleID] = 'Table2(remove)'[ModuleID]
        )
    )
RETURN
    IF ( result <> BLANK (), "Remove", "Add" )

 

2. Add all required column and add new filter column as filter :-

Thanks,

Samarth

 

 

ChoiJunghoon's avatar
ChoiJunghoon
Icon for Helper III rankHelper III
4 years ago

Thank you for your reply. 
But I must create the table by using DAX.

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Okay, then you can create a table with below dax:-

    VAR x0 =
SUMMARIZE (
    FILTER (
        Table1,
        NOT ( Table1[ModuleID] IN VALUES ( 'Table2(remove)'[ModuleID] ) )
    ),
    Table1[ModuleID],
    "SUM_", SUM ( Table1[StatusScore] ),
    "LastSequence", MAX ( Table1[Sequence] ),
    "location", MAX ( Table1[Location] )
)

    Output:-

     

    Thanks,

    Samarth

    • ChoiJunghoon's avatar
      ChoiJunghoon
      Icon for Helper III rankHelper III
      4 years ago

      Thank you for your reply. but. 
      Location column can not be applied with the "MAX" function. 

      Becuase it's not sequencial. 

       

      A2  5 D 

       

      Could you please check out that. 

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Okay. Can you try below code now:-

        VAR x0 = 
SUMMARIZE (
    FILTER (
        Table1,
        NOT ( Table1[ModuleID] IN VALUES ( 'Table2(remove)'[ModuleID] ) )
    ),
    Table1[ModuleID],
    "SUM_", SUM ( Table1[StatusScore] ),
    "LastSequence", MAX ( Table1[Sequence] ),
    "location",CALCULATE(max(Table1[Location]),Table1[Sequence] = MAX ( Table1[Sequence] ))
)

        Output:-

         

        Thanks,

        Samarth