March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 than 1 line and can be assigned both divisions, A and B. When a PO has 2 line items and assigned to division A in any line, i want to count this PO as division A PO, disregarding division B in the second line in the any line 1 or 2.
Hence, my question is how to distinctcount PO number and evaluate a PO as division A PO even it has second or third line that are assigned to division B or any division other than division A.
Example is below;
PO number/Division/Cost Center
8000000001/IT/001
8000000001/FIN/002
8000000002/FIN/004
8000000003/IT/003
8000000003/IT/001
Based on example above, when you count distinctvalues for IT, you should get 2 and 1 for FIN as you ignore second line for the PO number 8000000001 and consider it as IT PO.
Thank you very much.
Solved! Go to Solution.
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 ,
Whether the advice given by @AlB @Anonymous has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
Hello*
Try these 2 measures
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?
Note we have an ALL(Table1[Dept]) as last parameter on the main calculate . We need that to make the calculation across all departments. However, in the countrows we want to limit the count to the department currently on the filter context (for instance in the rows of your table visual). After the ALL(Table1[Dept]) that info will be lost, so as first step we get the current department through the SELECTEDVALUE() and store it in a variable. Then we use it to filter for that department in the COUNTROWS()
|
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. |
Thanks for bearing my comments. I've tried this formula with different databases too and it works perfectly. My understanding of this formula is below;
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |