Count Unique And Condition from other column

Anything related to PowerPivot and DAX Formuale
yp.ash
Posts: 4
Joined: Thu Oct 15, 2020 4:16 pm

Count Unique And Condition from other column

Postby yp.ash » Thu Oct 15, 2020 4:31 pm

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

RamanaV
Posts: 60
Joined: Thu Oct 19, 2017 12:57 pm

Re: Count Unique And Condition from other column

Postby RamanaV » Thu Oct 15, 2020 5:38 pm

Try this -
Count Unique Values = DISTINCTCOUNT('Table'[ID NUMBERS ])
Count Unique Accept Values = CALCULATE([Count Unique Values], 'Table'[CATEGORY] = "ACCEPT")
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

yp.ash
Posts: 4
Joined: Thu Oct 15, 2020 4:16 pm

Re: Count Unique And Condition from other column

Postby yp.ash » Thu Oct 15, 2020 7:05 pm

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!

RamanaV
Posts: 60
Joined: Thu Oct 19, 2017 12:57 pm

Re: Count Unique And Condition from other column

Postby RamanaV » Thu Oct 15, 2020 10:00 pm

Could you share your sample data?
The data that you have and the result you are expecting out of it.
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

yp.ash
Posts: 4
Joined: Thu Oct 15, 2020 4:16 pm

Re: Count Unique And Condition from other column

Postby yp.ash » Thu Oct 15, 2020 11:11 pm

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!

RamanaV
Posts: 60
Joined: Thu Oct 19, 2017 12:57 pm

Re: Count Unique And Condition from other column

Postby RamanaV » Sat Oct 17, 2020 8:43 am

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
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

yp.ash
Posts: 4
Joined: Thu Oct 15, 2020 4:16 pm

Re: Count Unique And Condition from other column

Postby yp.ash » Tue Oct 20, 2020 3:11 am

Thank you for the reply but I need those duplicated rows in Data.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests

cron