Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Scenario:
Suppose I want to get the first order information for each customer in each day. How can we achieve this? Here is an example with DAX and Power Query solution.
Table Used:
Tab:
Requirement:
We need to group by 'Customer' and 'Date'. And we may find the 'Item' and 'Quantity' corresponding to the first 'OrderID' in each group.
Method in DAX:
In DAX, we may create a calculated table as below.
Table 2 =
SUMMARIZE (
Tab,
Tab[Customer],
Tab[Date],
"Item",
VAR firstorderid =
MIN ( Tab[OrderID] )
RETURN
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[Item]
),
"OrderID",
VAR firstorderid =
MIN ( Tab[OrderID] )
RETURN
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[OrderID]
),
"Quantity",
VAR firstorderid =
MIN ( Tab[OrderID] )
RETURN
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[Quantity]
)
)
We need to follow below three steps to get the result.
SUMMARIZE (
Tab,
Tab[Customer],
Tab[Date],
...
...
)
VAR firstorderid = MIN ( Tab[OrderID] )
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[Item]
)
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[OrderID]
)
MAXX (
FILTER ( ALLEXCEPT ( Tab, Tab[Customer], Tab[Date] ), [OrderID] = firstorderid ),
[Quantity]
)
Result:
Method in Power Query:
You can add two new step with below m codes.
#‘Grouped Rows’
=Table.Group(#"Changed Type", {"Customer", "Date"}, {{"Tab", each Table.Min(_,"OrderID") }})
#'Expanded Tab'=
Table.ExpandRecordColumn(#"Grouped Rows", "Tab", {"Item", "OrderID", "Quantity"}, {"Item", "OrderID", "Quantity"})
The steps are the same with DAX method but in different language.
Conclusion:
The above scenario is often used in finding the corresponding information of the corresponding value in each group. For example, we want to get the information of the first check-in person in each project group every day. Or we'd like to know the person who does best in specific KPI in each month.
Hope this article helps everyone with similar questions here.
Author: Allan Qin
Reviewer: Ula Huang, Kerry Wang
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.