Page 1 of 1

Count Unique And Condition from other column

Posted: Thu Oct 15, 2020 4:31 pm
by yp.ash
Hi,
I have a problem getting a Dax FORMULA to count uniques from one column (ID NUMBERS column) ONLY for values that are "accept" in CATEGORY COLUMN.

DATE ID NUMBERS CATEGORY
01/01/2020 1 ACCEPT
01/01/2020 1 ACCEPT
02/01/2020 2 FAIL
02/01/2020 3 ACCEPT
02/01/2020 4 ACCEPT
02/01/2020 4 ACCEPT
02/01/2020 5 ACCEPT
03/01/2020 6 FAIL
03/01/2020 6 FAIL

THE RESULT
date count unique
01/01/2020 1
02/01/2020 3
03/01/2020 0


Thanks

Re: Count Unique And Condition from other column

Posted: Thu Oct 15, 2020 5:38 pm
by RamanaV
Try this -
Count Unique Values = DISTINCTCOUNT('Table'[ID NUMBERS ])
Count Unique Accept Values = CALCULATE([Count Unique Values], 'Table'[CATEGORY] = "ACCEPT")

Re: Count Unique And Condition from other column

Posted: Thu Oct 15, 2020 7:05 pm
by yp.ash
Thanks! it works great.
Another question please:
I have another column in this table with sales amounts and I want to sum all those sales amount that are unique and cateory accept.
what is the dax formula for that?
Thanks!

Re: Count Unique And Condition from other column

Posted: Thu Oct 15, 2020 10:00 pm
by RamanaV
Could you share your sample data?
The data that you have and the result you are expecting out of it.

Re: Count Unique And Condition from other column

Posted: Thu Oct 15, 2020 11:11 pm
by yp.ash
DATA

DATE ID NUMBER CATEGORY SALES AMOUNT
01/01/2020 1 ACCEPT 1000
01/01/2020 1 ACCEPT 1000
02/01/2020 2 FAIL 2000
02/01/2020 3 ACCEPT 1500
02/01/2020 4 ACCEPT 750
02/01/2020 4 ACCEPT 750
02/01/2020 5 ACCEPT 820
03/01/2020 6 FAIL 3000
03/01/2020 6 FAIL 3000

RESULT

DATE COUNT AMOUNT
01/01/2020 1 1,000
02/01/2020 3 3,070
03/01/2020 0 0

The amount on 02/01/20 is 1500+750+820=3070
Thanks!

Re: Count Unique And Condition from other column

Posted: Sat Oct 17, 2020 8:43 am
by RamanaV
I think that you can clean your data before loading so that your DAX formulas will be simple as you will have only the necessary data.
Go to Power Query.
Filter CATEGORY column to have only ACCEPT values
Select ID NUMBERS column and remove duplicates
Load the table

Total Amount = SUM(TABLE(SALES AMOUNT)) will give the result

Re: Count Unique And Condition from other column

Posted: Tue Oct 20, 2020 3:11 am
by yp.ash
Thank you for the reply but I need those duplicated rows in Data.