Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GrimReaperX
Helper II
Helper II

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 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.

1 ACCEPTED SOLUTION

@GrimReaperX 

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]))

 

SU18_powerbi_badge

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.

 

View solution in original post

8 REPLIES 8
v-henryk-mstf
Community Support
Community Support

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

Anonymous
Not applicable

Hello*

 

Try these 2 measures

 

calculation IT =
var ponbr=selectedvalue(Feuil2[PO Number])
var _step1=
filter(
ADDCOLUMNS (
SUMMARIZE ( Feuil2, Feuil2[PO Number] ),
"@on IT",
CALCULATE (
COUNT ( Feuil2[Division] ),
ALLEXCEPT ( Feuil2, Feuil2[PO Number] ),
Feuil2[Division] = "IT"
)
),[@on IT]>0)



var result=
countrows(_step1)
return
result
 
calculation FIN =
var ponbr=selectedvalue(Feuil2[PO Number])
var _step1=
filter(
ADDCOLUMNS (
SUMMARIZE ( Feuil2, Feuil2[PO Number] ),
"@on IT",
CALCULATE (
COUNT ( Feuil2[Division] ),
ALLEXCEPT ( Feuil2, Feuil2[PO Number] ),
Feuil2[Division] = "IT"
)
),[@on IT]=0)



var result=
countrows(_step1)
return
result
AlB
Super User
Super User

Hi @GrimReaperX 

How do you determine from the data whether 8000000001  has to be assigned to IT or FIN?

 

SU18_powerbi_badge

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.

@GrimReaperX 

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]))

 

SU18_powerbi_badge

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? 

@GrimReaperX 

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()  

 

SU18_powerbi_badge

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;

VAR currentDept_ = SELECTEDVALUE(Table1[Dept]) --> If Dept column has one value return the following result
RETURN
CALCULATE(
VAR auxT_ = ADDCOLUMNS(ALL(Table1[PO]), "@CC", CALCULATE(MIN(Table1[CostCenter]))) --> This expression creates a virtual table including all values from Table1 and creates additional column of Cost center. How MIN function affects newly createdCostCenter column, why MIN is used?  

VAR auxT2_ = ADDCOLUMNS(auxT_, "@Dept", CALCULATE(MAX(Table1[Dept]))) --> 
Reflects previous table with additional line where MAX Dept from Table1 is added. My question is how to calculate MAX value of Dept as it is text value? What is the function of this formula here?

RETURN
COUNTROWS(FILTER(auxT2_, [@Dept] = currentDept_)) , ALL(Table1[Dept])) --> 
Returning final result as counting all rows in the virtual table auxT2 where DEPT = currentDept 
 
I would really appreciate if you can bear this question too as it is complex formula which i want to literally get.
 
Thank you very much

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.