Forum Discussion

GrimReaperX's avatar
GrimReaperX
Helper II
4 years ago
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 ...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    4 years ago

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

     

    		 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.

     

GrimReaperX's avatar
GrimReaperX
Helper II
4 years ago

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? 

AlB's avatar
AlB
Community Champion
4 years ago

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

 

 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.

 

  • GrimReaperX's avatar
    GrimReaperX
    Helper II
    4 years ago

    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