Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Not applicable

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.

 

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Anonymous
Not applicable

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


 



 

Anonymous
Not applicable

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 }

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

CNENFRNL
Community Champion
Community Champion

VAR Result = SUMX(ItemTable, [Occurences])

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors