Distinct count before date on row with specific value

[tallesquirino]

Hi everyone,

I'm somewhat new on PowerBI (I work with it from time to time) and I faced a task this week that I want to know how you guys would proceed with it.

I managed to create the DAX measure able to handle this case, but I'm not sure it's the best one (performance wise).

One of the requirements is to use DirectQuery and if I filter more than one year, it returns a 'resultset 1000000 rows' error.

The challenge was to create 2 measures: number of orders before and after a specific date. The date is determined by a product type (let's say 000) bought in one of the orders.

user_id	order_id	product_id	order_date
user	AAA	123	01-01-2018
user	BBB	456	01-02-2019
user	CCC	000	01-03-2020
user	DDD	789	01-04-2021

In this scenario the desired output is:

user_id	orders	orders before 000	orders after 000
user	4	2	1

I didn't find much on the internet in terms of how to select the max date given a string, feel free to share with us 🙂

Here's the DAX formula for reference, if anyone wants it.

Would you do it any different?

before Premium = 
var currentUser = SELECTEDVALUE(VW_SALES[USER])
var lastDt = 
    MAXX(
        FILTER(
            VW_SALES,
            VW_SALES[TYPE] = "product" && VW_SALES[BUSINESS_LEVEL] = "Livraison premium" && VW_SALES[USER] = currentUser
        ),
        VW_SALES[ORDER_DATE]
    )
var qtyBefore =
COUNTROWS(
    FILTER(
        SUMMARIZE(VW_SALES,VW_SALES[USER],VW_SALES[ORDER_DATE]),
        VW_SALES[ORDER_DATE] < lastDt && VW_SALES[USER] = currentUser
    )
)
RETURN
qtyBefore

[DataInsights · Accepted solution]

@tallesquirino,

Here's a different approach using CALCULATE. Is the requirement to count distinct ORDER_ID or ORDER_DATE? I went with ORDER_ID since I'm guessing there could be multiple orders on the same day for a user. Also, is the user being selected via a slicer? In a star schema, there would be a user dimension table with a relationship to the fact table (view), and the slicer would use the dimension table. That would allow you to remove the currentUser variable from the measure (the filter context would flow from the dimension table to the fact table).

before Premium =
VAR currentUser =
    SELECTEDVALUE ( VW_SALES[USER] )
VAR lastDt =
    CALCULATE (
        MAX ( VW_SALES[ORDER_DATE] ),
        VW_SALES[TYPE] = "product",
        VW_SALES[BUSINESS_LEVEL] = "Livraison premium",
        VW_SALES[USER] = currentUser
    )
VAR qtyBefore =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( VW_SALES, VW_SALES[USER], VW_SALES[ORDER_ID] ),
            VW_SALES[ORDER_DATE] < lastDt,
            VW_SALES[USER] = currentUser
        )
    )
RETURN
    qtyBefore

Alternatively, you could try DISTINCTCOUNT:

before Premium =
VAR currentUser =
    SELECTEDVALUE ( VW_SALES[USER] )
VAR lastDt =
    CALCULATE (
        MAX ( VW_SALES[ORDER_DATE] ),
        VW_SALES[TYPE] = "product",
        VW_SALES[BUSINESS_LEVEL] = "Livraison premium",
        VW_SALES[USER] = currentUser
    )
VAR qtyBefore =
    CALCULATE (
        DISTINCTCOUNT ( VW_SALES[ORDER_ID] ),
        VW_SALES[ORDER_DATE] < lastDt,
        VW_SALES[USER] = currentUser
    )
RETURN
    qtyBefore