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.
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.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |