D count Column in Power Query

[FatBlackCat30]

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?

[az38]

Hi @FatBlackCat30 

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

LinkedIn

[FatBlackCat30]

@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 · Accepted solution]

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

@az38  worked beautifully. thank you for the continued support