Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to reference columns in a summarised table

I have written the following DAX code for a measure   VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id])) VAR Result = SUMX(ItemTable, ItemTable[O...
  • Anonymous's avatar
    Anonymous
    5 years ago

    The (my) problem was more to do with understanding how EVALUATE works. This worked just fine.


     DEFINE

        VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id]))
        
    EVALUATE

        ROW("ResultTable", SUMX(ItemTable, [Occurences]))
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for your reply.

I am testing this in DAXStudio and get an error when I try to do this.  This is what I am trying to run

 

 EVALUATE
(
    VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id]))

    VAR Result = SUMX(ItemTable, [Occurences])
    
    RETURN

        Result
)

and this is the error it gives me.

 

This works;

 EVALUATE
(
    VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id]))

    //VAR Result = SUMX(ItemTable, [Occurences])
    
    RETURN

        ItemTable
)

The problem seems to be in the SUMX where it is trying to access the 'Occurences' column from ItemTable.

 

ERD's avatar
ERD
Community Champion
5 years ago

Anonymous ,

SUMX will return a scalar value, not a table. That's what your error tells you.

Add your code between curly brackets: { }.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your reply. For clarity, this is what my code currently looks like.

     EVALUATE
    (
        VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id]))

        VAR Result = SUMX(ItemTable, ItemTable[Occurences])
        
        RETURN

        Result
    )

    I am expecting Result to come back as a Scalar value. The sum of all the entries in the 'Occurences' column. So I am not sure I understand what you are suggesting. The error I have now is this;

    It seems the issue is that it cannot identify the 'ItemTable' that I have just created (albeit virtually).

    This code is being created as a Measure on the Merge table, which is real. It seems that this might be a context issue, but I have specifically indicated ItemTable, but it does not know what that is.

    Apologies if I have misunderstood what you are saying, I am still very much learning how to use DAX.

    Thanks


     



     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Got it, this is what you meant

       

       EVALUATE
      (
          VAR ItemTable = SUMMARIZE(Merge1, Merge1[Item Code], "Occurences", DISTINCTCOUNT(Merge1[Order Id]))

          VAR Result = {SUMX(ItemTable, [Occurences])}
          
          RETURN

          Result
      )

      EVALUATE has to return a table! 🙂

      Thanks

       

      • ERD's avatar
        ERD
        Community Champion
        5 years ago

        Put the code between curly brackets to return a single scalar value: 

        Evaluate

        { code }