Count rows of different columns according to an ID

[Syndicate_Admin]

Good to all,

I hope you can help me with the following problem, I have a table where there are 4 variables:

Store is where a product was purchased, ID of the person who bought it, Tool that was used during the purchase (in the case of Customer1 I buy 2 product and use an ipad, in the case of Customer 2, I do not use anything but if I buy 1 product) and Product purchased

Shop	ID	Tool	Product
Shop 1	Cliente1	Ipad
Shop 1	Cliente1		Product 1
Shop 1	Cliente1		Product 2
Shop 1	Cliente1
Shop 1	Cliente2
Shop 1	Cliente2		Product 1
Shop 1	Cliente2
Shop 1	Cliente2

I need to show in Power BI the following:

Shop	Using Tool	Product Sale with Tool	No use of Tool
Shop 1	1	1	1

I need to show by stores the times that the tool was used, of the cases that did use the tool, how many to buy some product and those who bought something but did not use the tool.

I hope you understand the question and your help, thank you very much!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Syndicate_Admin]

Thank you very much for the answer, but I have a problem with the measure "Clients who bought using a tool" which is if a customer uses the ipad and I do not buy product, he tells me as if I use the product and buy, but I should not.

For example here instead of 3 I need to say 2

[v-zhangti · Accepted solution]

Hi, @Syndicate_Admin 

You can try the following methods.

Column:

Use bought = 
Var maxproduct=CALCULATE(MAX('Table'[Product]),ALLEXCEPT('Table','Table'[ID]))
return
IF(maxproduct<>BLANK(),1,0)

Use tool = 
Var maxtool=CALCULATE(MAX('Table'[Tool]),ALLEXCEPT('Table','Table'[ID]))
return
IF(maxtool<>BLANK(),1,0)

Result:

Measure:

Clients who bought using a tool = 
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Use bought]=1&&[Use tool]=1))

No use of Tool = 
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Use tool]<>1))

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where i can download the PBI file.