Forum Discussion

FatBlackCat30's avatar
FatBlackCat30
Microsoft Employee
6 years ago
Solved

D count Column in Power Query

Hello all,   I am wanting to create a Custom Column in Power Query :   1. I need to count the number of unique items per customer. BUT I dont want to include the items "All Other" or "Both" in th...
Need Help
  • az38's avatar
    az38
    6 years ago

    FatBlackCat30 

    try this

    Measure = 
var _countItems = CALCULATE(DISTINCTCOUNT('Table'[Items]);ALLEXCEPT('Table';'Table'[Customer]);NOT ('Table'[Items]  IN ({"Both"; "All Other"})))
RETURN
IF(_countItems < 1; 0; _countItems)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

FatBlackCat30's avatar
FatBlackCat30
Microsoft Employee
6 years ago

az38 this works perfectly, no requirement to use Power Query. 

 

is there a way I can add a 0 for the two values I am not counting in the formula

 

thanks for the help

az38's avatar
az38
Community Champion
6 years ago

FatBlackCat30 

try this

Measure = 
var _countItems = CALCULATE(DISTINCTCOUNT('Table'[Items]);ALLEXCEPT('Table';'Table'[Customer]);NOT ('Table'[Items]  IN ({"Both"; "All Other"})))
RETURN
IF(_countItems < 1; 0; _countItems)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

  • FatBlackCat30's avatar
    FatBlackCat30
    Microsoft Employee
    6 years ago

    az38  worked beautifully. thank you for the continued support