cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LGXSteve
Helper I
Helper I

How to reference columns in a summarised table

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

1 ACCEPTED SOLUTION
LGXSteve
Helper I
Helper I

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

View solution in original post

7 REPLIES 7
LGXSteve
Helper I
Helper I

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

View solution in original post

LGXSteve
Helper I
Helper I

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.

LGXSteve_0-1624013526105.png

 

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.

 

@LGXSteve ,

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;

LGXSteve_0-1624260649354.png

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


 



 

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

 

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

Evaluate

{ code }

CNENFRNL
Super User III
Super User III

VAR Result = SUMX(ItemTable, [Occurences])

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors