Expanded Table Behavior in DAX and Malloy

Expanded tables are a core concept in DAX, Malloy has something similar although with a default behavior:).

To see the difference let’s build the same Model in DAX and Malloy and see where it is the same and where it differ.

The Model is based on TPC-H Dataset, it is a simple model as it contains only 1 Base Table ” Lineitem”

The Same Model using Malloy

you can download the Malloy here : it is just a text file

Count the Number of customers

Malloy : results 999 982

Query: custoners_bought_something is {  
    aggregate: cnt is count( distinct customer.C_CUSTKEY)
                 }

DAX : 1 500 000

I know the table contains 1.5 M, so why Malloy is giving me wrong results, it turn out , it is by design, Malloy consider only the customers that bought something in lineitem, you can see it from the SQL Generated

DAX by default ignore the “graph” if the measure target only 1 table, to get the number of customers who bought an item, you need something like this

Take away

Maybe I am biased but I think DAX behavior make more sense, if I target only a table then the graph should be ignored, I think the relationship should be used only when I use fields from different tables.

3 thoughts on “Expanded Table Behavior in DAX and Malloy”

  1. Your query is within Lineitems so it is doing what you asked. If you want to query customers you can query the table directly with

    “`
    query: table(‘customers.parquet’)->{aggregate: c is count()}
    “`

    if you want to make customers an object you want to query often make it a source:

    “`
    source: customers is table(‘customers.parquet){}
    query: customers->->{aggregate: c is count()}
    “`

    Liked by 1 person

    1. Thank lloyd, I really appreciate you taking the time to reply, I was hoping that the default behavior of Malloy will be changed to include relationship only when a group by or a filter is included in the Query and not by default as it is today.

      Like

Leave a Reply to lloyd Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: