cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EnrichedUser
Helper II
Helper II

Distinct Count of User by Action with Minimum Threshold

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

BranchIDUserIDTransactionTypeLedgerDate
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDITEM.MOVE2/24/2021
CL20RBYRDITEM.MOVE2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.SHIP2/24/2021
CL21DSMITHORD.SHIP2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20DSMITHORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20RBYRDORD.PICK2/24/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHITEM.MOVE2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHITEM.MOVE2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20DSMITHORD.PICK2/23/2021
CL20RBYRDITEM.MOVE2/23/2021
CL20RBYRDORD.PICK2/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:

LedgerDateUserIDCount of ORD.PICKThreshold
2/24/2021RBYRD64
2/24/2021DSMITH54
2/23/2021RBYRD14
2/23/2021DSMITH114

 

Expected Output:

 

LedgerDateDistinct Count of Users above threshold for ORD.PICK
2/24/20212
2/23/20211

 

 

Notes:

 

Pickers =
CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]), ItemLedger[TransactionType] = "ORD.PICK")
 
was able to tell me how users performed the action, but leaves out a minimum threshold.
 
I was trying to use summarizecolumns to do the grouping but I lost the row context and wasnt able to see it by date. 
 
I also tried using just summarize

GroupedTable =
SUMMARIZECOLUMNS(ItemLedger[UserID],ItemLedger[TransactionType],'Date'[Date],
FILTER(ALL(ItemLedger), ItemLedger[TransactionType] = "ORD.PICK"),
"Picks", [Lines Picked])
1 ACCEPTED SOLUTION
EnrichedUser
Helper II
Helper II

Solution:

 

Pickers =
VAR Table1 =
SUMMARIZE(ItemLedger,ItemLedger[UserID], 'Date'[Date], "Daily Picks", CALCULATE(COUNTROWS(ItemLedger), FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")),
"Did Pick", CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]),FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")))
RETURN

SUMX(FILTER(Table1, [Daily Picks] > 15),[Did Pick])
 
 
The variable table allowed me to reference the calculated fields of "Did Pick" and "Daily Picks" in SUMX

I could likely simplify using only one filter. 
 
The threshold used in this example is > 15

View solution in original post

3 REPLIES 3
EnrichedUser
Helper II
Helper II

Solution:

 

Pickers =
VAR Table1 =
SUMMARIZE(ItemLedger,ItemLedger[UserID], 'Date'[Date], "Daily Picks", CALCULATE(COUNTROWS(ItemLedger), FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")),
"Did Pick", CALCULATE(DISTINCTCOUNT(ItemLedger[UserID]),FILTER(ItemLedger, ItemLedger[TransactionType] = "ORD.PICK")))
RETURN

SUMX(FILTER(Table1, [Daily Picks] > 15),[Did Pick])
 
 
The variable table allowed me to reference the calculated fields of "Did Pick" and "Daily Picks" in SUMX

I could likely simplify using only one filter. 
 
The threshold used in this example is > 15
amitchandak
Super User
Super User

@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

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors