topic Re: Count unique per other columns in DAX Commands and Tips

Count unique per other columns

MasterSonic — Wed, 21 Dec 2022 10:18:54 GMT

Hello,

Code	Plan	Status
101	Buy	Ready
102	Sell	Ready
102	Sell	Ready

I would like to count unique code by Status and Planm but I have duplicates in Code column.

So on Report, I could drop Plan and "Count of Status" showing 1 for Plan SELL, based on table above.

I have this measurment but it doesn't do the job, as is showing 2, counting Code per plan ONLY, without looking at Status

Measur1 = CALCULATE(DISTINCTCOUNT('Table'[Code]),ALLEXCEPT('Table'[Plan]))

Is there any way to perform calculaction please?

Re: Count unique per other columns

FreemanZ — Wed, 21 Dec 2022 10:28:22 GMT

hi MasterSonic

try like:

Measure2 =

COUNTROWS(

SUMMARIZE(

TableName,

TableName[Plan],

TableName[Status]

)

Re: Count unique per other columns

MasterSonic — Wed, 21 Dec 2022 10:42:17 GMT

Hi,
thanks but It does not give the wanted result.

lets extend my table

Code	Plan	Status
101	Buy	Ready
102	Sell	Ready
102	Sell	Ready
103	Sell	Ready

Now it should showing col=Plan value=Sell should be 2
As we have code duplicated.

I would like to count Code per Plan where value in column Status is Ready, but do not count duplicated Codes. Maybe this will help

Re: Count unique per other columns

FreemanZ — Wed, 21 Dec 2022 11:43:32 GMT

not sure if i get you, try this

Measure2 =

COUNTROWS(

SUMMARIZE(

TableName,

TableName[Plan],

TableName[Code]

)

Re: Count unique per other columns

MasterSonic — Wed, 21 Dec 2022 12:22:46 GMT

Thank You for help I have added below and I got what wanted.

Measure2 =

COUNTROWS(

SUMMARIZE( FILTER( Table,Table[Status] = "Ready"),

TableName[Plan],

TableName[Code]

)