Writing DAX in Power BI for Facts and Dimensions

Question

Let's say in Power BI I have the following set up:

d_date (this is a date dimension)
d_customers (this is a customer dimension)
f_orders (this is a fact table)

f_orders connects to d_date on date_id and connects to d_customers on customer_id.

I want to create a dax measure that shows the list of distinct customers, along with their name, for each date.

How could I do so?

(I am a novice at Dax and somewhat new to Power BI)

Expected output

Table 1
    order date     Customer Name
    2020-01-01     John Doe
    2020-01-01     James Simpson
    2020-01-03     Emilia Clarke
    ...
    2020-12-31     Jamie Lanister


Table 2

    order date     distinct_customer_count
    2020-01-01     2
    2020-01-03     1
    ...
    2020-12-31     1

My goal is not so much to produce the output as to see a simple example of using DAX to produce output from 2 tables.

Can you post an example of what the expected result should look like? A count along with a customer name as a measure??? Say, distinct count of customers for a day is 120, where would customer name come in? — teylyn
I'm basically just looking for any simple example for using multiple tables in a dax statement. Can be super duper simple if need be, just need an example. — Chicken Sandwich No Pickles
The table 1 can be done completely without any DAX, just create a table and pull date and customer name into the table. The second table is what I explain in my answer. — teylyn

sergiom sergiom · Accepted Answer · 2021-03-05T11:03:03

You are asking for a measure returning a list. This cannot be done directly, since measures must return a scalar, unless you mean to build a string with all the customers. (this could be done using CONCATENATEX(), but doesn't work well when more than few customers are to he shown)

From your desired output I see that you would like to see a table visual with date and customer who placed an order for that date. To do so it suffice to create a customers count measure like follows

# Customers = DISTINCTCOUNT( f_orderes[customer_id] )

and to create a table visual with d_date[date], d_customer[name] and [# Customers]

using only d_date[date], and [# Customers] you should get the Table 2

to see some code using both tables, you might compute calculated tables, like for instance, moving to a real dataset with customers, date and sales, an easy implementation of table 1 is

SUMMARIZE ( Sales, 'Date'[Date], Customer[Name] )

and here is the same code to be tested on dax.do

and for table 2

FILTER (
    ADDCOLUMNS (
        ALL ( 'Date'[Date] ),
        "# Customers", CALCULATE ( DISTINCTCOUNT ( Sales[CustomerKey] ) )
    ),
    NOT ISBLANK ( [# Customers] )
)

the FILTER on NOT ISBLANK() is needed to remove the dates with no sales

here is the code on dax.do

it's also possible to use the SUMMARIZECOLUMNS, but SUMMARIZECOLUMNS is a more advanced function that cannot be used in DAX measures.

SUMMARIZECOLUMNS (
    'Date'[Date],
    "# Customers", DISTINCTCOUNT ( Sales[CustomerKey] )
)

here is the dax.do link for this last code snippet

Writing DAX in Power BI for Facts and Dimensions

2 Answers