Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My goal is provide the distinct count of users who performed a specifc action (1 action = 1 record under TransactionType) more than a threshold.
I have a table called ItemLedger. There are several columns but the ones of importance are:
BranchID, UserID, TransactionType, LedgerDate
BranchID | UserID | TransactionType | LedgerDate |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ITEM.MOVE | 2/24/2021 |
CL20 | RBYRD | ITEM.MOVE | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/24/2021 |
CL20 | DSMITH | ORD.SHIP | 2/24/2021 |
CL21 | DSMITH | ORD.SHIP | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | RBYRD | ORD.PICK | 2/24/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ITEM.MOVE | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ITEM.MOVE | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | DSMITH | ORD.PICK | 2/23/2021 |
CL20 | RBYRD | ITEM.MOVE | 2/23/2021 |
CL20 | RBYRD | ORD.PICK | 2/23/2021 |
My goal is provide the distinct count of users who performed a specifc action more than a threshold.
Using a specific example based on the above sample, I would like to determine how many users picked more than 4 times for each day.
Intermediate Table Example:
LedgerDate | UserID | Count of ORD.PICK | Threshold |
2/24/2021 | RBYRD | 6 | 4 |
2/24/2021 | DSMITH | 5 | 4 |
2/23/2021 | RBYRD | 1 | 4 |
2/23/2021 | DSMITH | 11 | 4 |
Expected Output:
LedgerDate | Distinct Count of Users above threshold for ORD.PICK |
2/24/2021 | 2 |
2/23/2021 | 1 |
Notes:
Solved! Go to Solution.
Solution:
Solution:
@EnrichedUser , Create a measures like
Sumx(Summarize(ItemLedger,ItemLedger[UserID],'Date'[Date],
"_1",CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]), filter(ItemLedger,ItemLedger[TransactionType] = "ORD.PICK"))), [_1])
infact remove filter and use slicer to control type
Thank you so much for getting back to me so quickly! Unfortutaly, this solution did not include the the threshold pieces what I needed the help with. However, it did point me to focus more on trying to solve with sumx.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |