Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Sometimes it isn't so obvious or intuitive in which functions calculated tables/columns can be used as arguments. Does anyone know where I can find documentation or articles that would help to understand this better?
@marcorusso perhaps an article in sqlbi that I coudn't find reviews this?
Thank you,
NAOS
SUM is a shortcut for SUMX, I suggest you look at their syntax and examples on https://dax.guide
However, I stronlgy suggest you learn the DAX fundamentals first: row context, filter context, context transition.
There are many resources out there, including Microsoft documentation. You can also use this free video course: Introducing DAX Video Course - SQLBI
Upon reading my question I think that, on trying to simplify it, I ended up asking the wrong question entirely.
What I'm trying to understand is how is it defined which DAX functions can use a DAX table or columns which are calculated within the measure as a variable?
For example this measure doesn't work:
example1 =
VAR _calculatedTable =
SELECTCOLUMNS (
'FactTable',
"businessunit", 'FactTable'[Business Unit],
"area", SUM ( 'FactTable'[Area] )
)
RETURN
sum(_calculatedTable([area]) )
This measure does work:
example2 =
VAR _calculatedTable =
SELECTCOLUMNS (
'FactTable',
"businessunit", 'FactTable'[Business Unit],
"area", SUM ( 'FactTable'[Area] )
)
RETURN
SUMX(_calculatedTable , [area] )
Why can SUMX take an expression-generated table as argument whereas SUM can't?
We already have DAX Guide for that.
For example:
- UNION gets tables as arguments: UNION - DAX Guide
- TRIM gets any text: TRIM - DAX Guide
Now, remember that any table expression in DAX can be converted to a scalar value as long as it has one column and zero or one rows. Therefore, you can try to pass a table to any function that accepts a scalar value (including TRIM - you have a runtime error if the table cannot be converted, but it's not a syntax error), whereas you cannot provide a scalar value when a table value is expected (this is a syntax error).
I hope it helps.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |