CODEWITHSUNDEEP
powerbidaxdata-warehousepowerbi-desktop
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Writing DAX in Power BI for Facts and Dimensions

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.

Upvotes: 0

Views: 944

Answers (2)

sergiom
sergiom

Reputation: 4887

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

Upvotes: 1

teylyn
teylyn

Reputation: 35990

Create a measure that counts distinct customers in f_orders. If you use that measure in a visual that groups/filters by date, then that measure will show the correct values.

For example a chart with date on the X axis and the distinct customer count as the value will then show the distinct count for each day.

In Power BI/DAX, you don't need to pre-calculate all kinds of scenarios because the measure will always get evaluated in the context of the filters of the page/visual.

Upvotes: 0

Related Questions