cancel
Showing results 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

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
Super User

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.

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

Best Regards,
Henry

Anonymous
Not applicable

Hello*

Try these 2 measures

calculation IT =
var ponbr=selectedvalue(Feuil2[PO Number])
var _step1=
filter(
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(
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
Super User

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.

Helper II

Hi @AlB,

It is determined based on cost center. Cost center 001 is for IT while 002 for Finance.

Thanks for quick turn around.

Super User

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.

Helper II

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?

Super User

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.

Helper II

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors