Forum Discussion
Solved
Counting distinct values based on criteria
Hello guys, I have a data where PO number, cost center and division is given. Based on specific cost center a PO is considered either division A or B. It is possible a PO number might have more ...
See attached file for a possible solution
Measure = VAR currentDept_ = SELECTEDVALUE(Table1[Dept]) RETURN CALCULATE( VAR auxT_ = ADDCOLUMNS(ALL(Table1[PO]), "@CC", CALCULATE(MIN(Table1[CostCenter]))) VAR auxT2_ = ADDCOLUMNS(auxT_, "@Dept", CALCULATE(MAX(Table1[Dept]))) RETURN COUNTROWS(FILTER(auxT2_, [@Dept] = currentDept_)) , ALL(Table1[Dept]))Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi GrimReaperX
How do you determine from the data whether 8000000001 has to be assigned to IT or FIN?
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi AlB,
It is determined based on cost center. Cost center 001 is for IT while 002 for Finance.
Thanks for quick turn around.
See attached file for a possible solution
Measure = VAR currentDept_ = SELECTEDVALUE(Table1[Dept]) RETURN CALCULATE( VAR auxT_ = ADDCOLUMNS(ALL(Table1[PO]), "@CC", CALCULATE(MIN(Table1[CostCenter]))) VAR auxT2_ = ADDCOLUMNS(auxT_, "@Dept", CALCULATE(MAX(Table1[Dept]))) RETURN COUNTROWS(FILTER(auxT2_, [@Dept] = currentDept_)) , ALL(Table1[Dept]))Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
I am trying to get the logic of this function but cannot relate SELECTEDVALUE within this function. Why it is used and necessary for this measure?
