Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to Solution.
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]))
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]))
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.
@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!
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
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!
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |