Forum Discussion
Solved
Distinct sum based on two columns
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 DISTINCTCOUNTNOBLANK, but I can´t tell PBI ...
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.
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.
