Measure that counts items with multiple values

[Anonymous]

Hi everyone,

What code would you use to create a measure for the following?

I need to know how many "Codes" have >1 Values within the column.

I would then input this measure into a tile, to display the number of "Codes" that have >1 Values.

In the screenshot below, AA123 has 1 value (ie. 5). BB987 has 3 values (ie. 10, 12, 14). CC000 has 1 value (ie. 20). DD999 has 3 values (ie. 30, 31, 32).

The final output should be 2; BB987 and DD999 has 3 non-duplicate values.

I have tried something like this, but to no avail:

DISTINCTCOUNT_Code = CALCULATE(DISTINCTCOUNTNOBLANK(SAMPLE_TABLE[CODE]), FILTER(SAMPLE_TABLE, ...))

Any help would be appreciated!

Thanks!

[Anonymous]

measure=calculate(distinctcount(table[Column]),filter(table,table[Column1]>1))

Add column and measure into table visual.

Note Replace column and column1 with your column names.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Hi PR20048119,

Thanks for the reply.

I tried the code you suggested;

Sample_Measure = CALCULATE(DISTINCTCOUNT(Sheet1[Code]),FILTER(Sheet1,Sheet1[Value]>1))

However, it does not provide the correct output (see screenshot below).

The output should be 0 for AA123, 1 for BB987, 0 for CC000, 1 for DD999.

In order words, it should count 1 if there were >1 non duplicate values that that code.

How would you solve this?

Thanks!

[Anonymous]

So you want distinctcount only.

Try this
Measure= distinctcount(table[value])-1

Add id column and measure in table visual.

Thanks
Pravin

[Anonymous]

Hi PR20048119,

Thanks, your code solved the first part of the problem.

Sample_Measure_2 = DISTINCTCOUNT(Sheet1[Value])-1

Below is the screenshot for this.

At the same time, how can I add onto the above code, so that I can put this measure into a card? This card should show the value 2; BB987 counted once plus DD999 counted once.

Thanks!