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
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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors