@ruthpozuelo

Specifically, if you can write something that looks like below, this implies that the function *must* return a table.

CALCULATE ( [measure], OFFSET ( ... ) )

The setfilter args to CALCULATE must be one of:

1. A table expression
2. A simple predicate that references a column
3. One of the ALL family

If the arg is a function call, that function must return a table or be in the ALL family. Simple predicates are translated to FILTER expressions, which returns a table.

#DAXLang

@ruthpozuelo

INDEX and OFFSET return a single row. If this is a row from a one-column table, then it will be implicitly converted to a scalar if used in a context where a scalar is expected. This is normal behavior for table-valued functions.

WINDOW returns arbitrarily many rows depending on the context it is called in.

Using a fixed number for the first parameter of INDEX seems to me to be roughly equivalent to just writing it as a simple setfilter predicate for CALCULATE.

#DAXLang #DAX

Day 9 of #AdventOfCode in #PowerBI.

Today's challenge is another two-dimensional grid and accumulation of state. It ends up looking like the game Snake.

A good example of changing the cardinality of a fact table between the two halves.

Follow solutions and code: https://www.antifound.com/posts/advent-of-code-2022/#day-9-rope-bridge

https://adventofcode.com/2022/day/9

#MLang #DAXLang #PowerQuery

Day 7 of #AdventOfCode in #PowerBI

1. Much more involved and stateful parsing of the input. This lets us stretch our legs on writing #MLang functions to build a solution.
2. Realistic and useful representation of a filesystem if you ever need this in PBI -- a good example of an unwound hierarchy for #DAXLang.
3. Found a severe memory leak in M Intellisense.

Follow along and find solutions on my blog: https://www.antifound.com/posts/advent-of-code-2022/#day-7-filesystems

https://adventofcode.com/2022/day/7

https://community.powerbi.com/t5/Issues/Memory-leak-when-using-PQ-advanced-editor/idi-p/2954784#M88901

#PowerQuery

Day 3 of #AdventOfCode in #PowerBI.

All about grouping and set operations (and vindication for 0-indexing) in #PowerQuery / #MLang and #DAXLang.

Good exercise in preserving valuable source information through #ETL as well.

Take a look and follow along on my blog (code and solutions available): https://www.antifound.com/posts/advent-of-code-2022/

https://adventofcode.com/2022/day/3

I am doing this year’s Advent of Code in #PowerBI with #PowerQuery #MLang and #DAXLang

Follow along with my progress at https://www.antifound.com/posts/advent-of-code-2022/

Today’s (and future) solutions can be found in my AOC pbix file. It is a good example of dealing with source data that is grouped positionally and delimited with blank data rows. https://www.antifound.com/assets/aoc2022.pbix

#AdventOfCode https://adventofcode.com/2022/day/1