Forum Discussion
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 the count.
here is an example of the data and the outcome.
| Customer | Items | Customer | Items | Count | ||
| 1 | Plant | 1 | Plant | 2 | ||
| 1 | Cat | 1 | Cat | 2 | ||
| 2 | Dog | 2 | Dog | 1 | ||
| 3 | Car | 3 | Car | 1 | ||
| 4 | Both | 4 | Both | 1 | ||
| 4 | Bottle | 4 | Bottle | 1 | ||
| 5 | All Other | 5 | All Other | 0 | ||
| 6 | Phone | 6 | Phone | 2 | ||
| 6 | Tree | 6 | Tree | 2 |
Is there a good resource I can find info on formulas like this so I dont have to keep asking for help?
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
4 Replies
Is it mandatory to use Power Query?
It is a great, easy and very logical task for DAX:
Measure = var _countItems = CALCULATE(DISTINCTCOUNT('Table'[Items]);ALLEXCEPT('Table';'Table'[Customer]);NOT ('Table'[Items] IN ({"Both"; "All Other"}))) RETURN IF(SELECTEDVALUE('Table'[Items])="Both" || SELECTEDVALUE('Table'[Items])="All Other";0; _countItems)do not hesitate to give a kudo to useful posts and mark solutions as solution
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
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
