Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
112 | |
110 | |
72 | |
72 |