topic Re: How to reference columns in a summarised table in DAX Commands and Tips

How to reference columns in a summarised table

Anonymous — Fri, 18 Jun 2021 07:16:27 GMT

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[Occurences])
RETURN

Result

ItemTable gives me a simple table of Item Codes and a numeric column called Occurrences. I now want to sum all of the values in 'Occurences' using SUMX. I get an error though that says "Cannot find table ItemTable".

Having created my temporary table how can I now use it in other DAX expressions? I have seen this used in things like FILTER, so don't understand why I cannot use it in SUMX. What am I missing or not doing?

I have done some more research and this article (https://www.sqlbi.com/articles/table-an ... variables/) says that this,

ItemTable[Occurences]

cannot be done because you cannot reference columns in temporary tables. Hence the error. So, my approach needs to change. The point of the temporary table is that this filters down the original table. which maybe I have to do with a FILTER. Any advice on how to achieve what I want would be appreciated.

The reason why I have created a summarised table is to remove duplicate rows and give me a table where each Item Code only appears once. I can then use this to create a Pareto chart.

(I have asked this on Power Pivot Forum but had no responses yet, so apologies for asking the same question here as well).

Re: How to reference columns in a summarised table

CNENFRNL — Fri, 18 Jun 2021 09:32:41 GMT

VAR Result = SUMX(ItemTable, [Occurences])

Re: How to reference columns in a summarised table

Anonymous — Fri, 18 Jun 2021 10:53:41 GMT

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.

Re: How to reference columns in a summarised table

Anonymous — Fri, 18 Jun 2021 12:14:01 GMT

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]))

Re: How to reference columns in a summarised table

ERD — Fri, 18 Jun 2021 12:19:53 GMT

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.

Re: How to reference columns in a summarised table

Anonymous — Mon, 21 Jun 2021 07:33:28 GMT

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

Re: How to reference columns in a summarised table

Anonymous — Mon, 21 Jun 2021 09:03:26 GMT

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

Re: How to reference columns in a summarised table

ERD — Mon, 21 Jun 2021 09:47:04 GMT

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

Evaluate

{ code }