Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have 1 table for EE data which contains 14 columns with an EE # which reflect the 1 up reprts to (i.e. 11111 rpts to 2222 rpts to 3333 etc). The list also contins a column with just the person EE#.
I need to count how many times a specific EE# number appears in the 14 columns hopefull based on a user input.
The formula I thihnk will work is below which should count the values in each of the 14 columns:
The piece I cannot figure out and asking for help is how to create a Parameter or what ever method/tool a user can manuall enter the EE# (i.e. EMP # is automatically used as "[NumericInput] for below).
In perfect world once the user enters or has ability to select or free text enter the EMP # the list will filter to all the rows where this value is found and provides count?
<table name = HRR082, columns (L01 Person Name, L02 -L14 etc.)
VAR UserValue = SELECTEDVALUE(NumericInput[NumericInput])
RETURN
CALCULATE(
COUNTROWS(HRR082),
FILTER(
HRR082,
HRR082[L01 Person Number] = UserValue ||
HRR082[L02 Person Number] = UserValue ||
HRR082[L04 Person Number] = UserValue ||
HRR082[L05 Person Number] = UserValue ||
HRR082[L06 Person Number] = UserValue ||
HRR082[L07 Person Number] = UserValue ||
HRR082[L08 Person Number] = UserValue ||
HRR082[L09 Person Number] = UserValue ||
HRR082[L10 Person Number] = UserValue ||
HRR082[L11 Person Number] = UserValue ||
HRR082[L12 Person Number] = UserValue ||
HRR082[L13 Person Number] = UserValue ||
HRR082[L14 Person Number] = UserValue ||
)
)
Solved! Go to Solution.
Hi @Spell
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Actually found different solution, I conc the 12 columns then user can just use advance filter with contains to get rows thx ofr help and replies.
Hi @Spell
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @Spell - create a dax measure to reference the employee slicer as below:
in field parameters itself we can configure the below and set Data Type to Whole Number.
Set Minimum & Maximum values (e.g., 10000 to 99999 if applicable).
EE_Count =
VAR UserValue = SELECTEDVALUE(EmployeeInput[EmployeeInput])
RETURN
CALCULATE(
COUNTROWS(HRR082),
FILTER(
HRR082,
HRR082[L01 Person Number] = UserValue ||
HRR082[L02 Person Number] = UserValue ||
HRR082[L03 Person Number] = UserValue ||
HRR082[L04 Person Number] = UserValue ||
HRR082[L05 Person Number] = UserValue ||
HRR082[L06 Person Number] = UserValue ||
HRR082[L07 Person Number] = UserValue ||
HRR082[L08 Person Number] = UserValue ||
HRR082[L09 Person Number] = UserValue ||
HRR082[L10 Person Number] = UserValue ||
HRR082[L11 Person Number] = UserValue ||
HRR082[L12 Person Number] = UserValue ||
HRR082[L13 Person Number] = UserValue ||
HRR082[L14 Person Number] = UserValue
)
)
Hope this helps.
Proud to be a Super User! | |
Thx of the input "create a dax measure" however that is the issue, I do not know how to create the measure which allows the user to input of select the value to be used? The value would be a specific EMPNO, so creating a slider does not really help, I need it to be a box where the number could be selected or typed in, not sure if that functionality exists?