Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi Community,
Tried to count how many products the employee worked, sounds easy at the beggining but I cant count the second column.
Tried usuing
Desire results | ||||
Product | Shift1 | Shift2 | Name | ProductsWorked |
a | Wade | Brian | Wade | 2 |
b | Dave | Roberto | Dave | 3 |
c | Seth | Ramon | Seth | 4 |
d | Brian | Brian | 3 | |
e | Roberto | Riley | Roberto | 2 |
f | Ramon | Gilbert | Ramon | 3 |
g | Jorge | Gilbert | Jorge | 1 |
h | Dan | Gilbert | 5 | |
i | Brian | Wade | Riley | 1 |
j | Roberto | Dave | Dan | 1 |
k | Ramon | Seth | ||
l | Ramon | Brian | ||
m | Dave | |||
n | Gilbert | Ramon | ||
o | Gilbert | Seth | ||
p | Gilbert | Seth |
Solved! Go to Solution.
Hi @Aucesar
You can refer to the following measure
1.Create a table to diaplay the user
2.Then create a measure
ProductionBatchWorked = var a=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift1])
var b=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift2])
var c=SUMMARIZE(UNION(a,b),'Table'[Shift1],"Distinct",DISTINCTCOUNTNOBLANK('Table'[ProductionBatch]))
return MAXX(FILTER(c,[Shift1] in VALUES('Table 2'[Shift2])),[Distinct])
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
my solution in DAX
https://1drv.ms/u/s!AiUZ0Ws7G26Rigc-Q_c7tkx3fX-1?e=vdm3x6
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Ahmedx / @Dhairya thanks so far, as the users are never happy, how to accomplish this new column?
Thanks
Desire results | ||||||
ProductionBatch | Product | Shift1 | Shift2 | Name | ProductsWorked | ProductionBatchWorked |
XX01 | a | Wade | Brian | Wade | 2 | 1 |
XX01 | b | Dave | Roberto | Dave | 3 | 3 |
XX01 | c | Seth | Ramon | Seth | 4 | 3 |
XX01 | d | Brian | Wade | Brian | 3 | 2 |
XX02 | e | Roberto | Riley | Roberto | 2 | 2 |
XX02 | f | Ramon | Gilbert | Ramon | 3 | 3 |
XX02 | g | Jorge | Gilbert | Jorge | 1 | 1 |
XX02 | h | Dan | Gilbert | 5 | 3 | |
XX02 | i | Brian | Ramon | Riley | 1 | 1 |
XX02 | j | Roberto | Dave | Dan | 1 | 1 |
XX02 | k | Ramon | Brian | |||
XX03 | l | Ramon | Seth | |||
XX03 | m | Dave | ||||
XX03 | n | Gilbert | Ramon | |||
XX03 | o | Gilbert | Seth | |||
XX03 | p | Gilbert | Seth |
Hi @Aucesar
You can refer to the following measure
1.Create a table to diaplay the user
2.Then create a measure
ProductionBatchWorked = var a=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift1])
var b=SUMMARIZE(ALLSELECTED('Table'),[ProductionBatch],[Shift2])
var c=SUMMARIZE(UNION(a,b),'Table'[Shift1],"Distinct",DISTINCTCOUNTNOBLANK('Table'[ProductionBatch]))
return MAXX(FILTER(c,[Shift1] in VALUES('Table 2'[Shift2])),[Distinct])
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Aucesar
If the provided solution helps you then please give it a kudo and mark my solution as accepted so that other can find it quickly on facing similar issue. Thank You!
Hey @Aucesar
You have to transform your data first please follow the below steps
Input:
Step1: Open Power Query Editor and unpivot Shift1 and Shift2 columns, and click on close and apply
Output:
Step2: Now create following measure
S1 =
CALCULATE(
COUNTX(TableA,TableA[Product]),
ALLEXCEPT(TableA,TableA[Value])
)
Step3: Plot your expected columns you will get the following output
If this helps you then please mark my solution as accepted so that others can find it quickly while facing similar issue. Thank You!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
73 | |
57 | |
38 | |
35 |
User | Count |
---|---|
83 | |
68 | |
59 | |
46 | |
45 |