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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.