How to tell which functions can take calculated tables/columns as arguments and which don't?

[NAOS]

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

[marcorusso]

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

[NAOS]

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?

[marcorusso]

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.