Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |