finding values not zero

[fklatecki]

Greetings,

This simple DAX formula is eluding me.

I have a report filtered by Supplier, and I wish to count the number of customers who have purchased some of their product in 2017 (Gross > 0 )

I wish to put up widget to show the # of customers in 2017, of course this value would change for each Supplier sliced.

(Slicing and filtering by Year 2017, user_id, manufacture_id)

As always the help of the community is always appreciated.

Best from Nova Scotia's beautiful Annpolis Valley.

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(DISTINCTCOUNT(Data[id]),Data[Gross]>0)

Select any year/Supplier via the Filter section/slicer.

I have assumed that id is the Customer id.

[fklatecki]

Not quite the solution to my issue, likely due to my poor explanation.

I have made some progress (I think).

ADF = COUNTAX(FILTER(gross_sales,[Sales 2017]>0),[Gross])

Sales 2017 = CALCULATE(SUM(gross_sales[Gross]),gross_sales[year]=2017)

along with manufacturer_id, and user_id, I have this sample table. In effect, I am looking to count the number of items in column ADF (the values are the actual number of transaction to comprise the Sales 2017 value).

manufacturer_id	user_id	Sales 2017	ADF
102	73	$0
102	74	$6,008	3
102	75	$0
102	76	$14,972	7
102	77	$4,934	10
102	78	$65,662	12
102	79	$0
102	80	$0
102	82	$0
102	83	$0
102	84	$1,325	4
102	85	$0
102	86	$9,487	10

The answer I am looking for in this sample, would be 6.

I very appreciate your time, with many thanks.

[Ashish_Mathur]

Hi,

=CALCULATE(COUNTA(Data[user_id]),Data[Sales 2017]>0)

Does this work?

[fklatecki]

Sorry no.

Dealer_Count = CALCULATE(COUNTA(gross_sales[user_id]),gross_sales[Sales 2017] > 0)  creates this message:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Thanks for your ongoing assistance.

Frank

[Ashish_Mathur]

Hi,

It works absolutely fine.

[fklatecki]

Greetings again, 

Again thank you for working through this issue with me.

I have isolated the issue down to my use of a measure with a measure, "Sales 2017" is itself a measure "active = CALCULATE(counta(gross_sales[user_id]),gross_sales[Sales 2017]>0)" resulting in the error message, "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." My data is "gross_sales".

I have replicated your Data table, where I have individual values in the "Sale 2017" column, not a measure. In the Data table your formula works as designed.

I have attached a sample of my report to this reply.

Is there a way to make my measure "Sales 2017" work within this scenario? Alternates?

https://e2fe3c147248bbb3-my.sharepoint.com/:f:/g/personal/fklatecki_indeal_org/EsKlQdulUCZEhdUkla6S3...

[Ashish_Mathur]

Hi,

I am now completely confused.  You are now bringing another table.  All this while you were talking about only 1 table.  Now there are two.  Please explain the question very clearly.  Do not be concerned with any formulas.  First explain the business problem clearly.

[fklatecki]

I apologize for causing confusion. Per my BI sample, I am working with the “gross_sales”. I am looking to determine the number of Suppliers (manufacturer_id) with purchase made in 2017 by Dealers(user_id), this would determine the number of “Active Dealers”.

I created the measure “Sales 2017” to isolate sales in 2017, and then filter by Supplier, by Dealer, and then counting the number of rows greater than zero.

Best

[fklatecki]

Greetings,

Looking for solution to my DAX formula issue?

Re: finding values not zero

Saturday

I apologize for causing confusion. Per my BI sample, I am working with the “gross_sales”. I am looking to determine the number of Suppliers (manufacturer_id) with purchase made in 2017 by Dealers(user_id), this would determine the number of “Active Dealers”.

I created the measure “Sales 2017” to isolate sales in 2017, and then filter by Supplier, by Dealer, and then counting the number of rows greater than zero.

[fklatecki]

I apologize for causing confusion. Per my BI sample, I am working with the “gross_sales”. I am looking to determine the number of Suppliers (manufacturer_id) with purchase made in 2017 by Dealers(user_id), this would determine the number of “Active Dealers”.

I created the measure “Sales 2017” to isolate sales in 2017, and then filter by Supplier, by Dealer, and then counting the number of rows greater than zero.