Forum Discussion
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[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).
7 Replies
VAR Result = SUMX(ItemTable, [Occurences])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 runEVALUATE
(
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.
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.
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
