Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Any help would be appreciated!
Thanks!
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.
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!
Hi PR20048119,
Thanks, your code solved the first part of the problem.
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!
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
86 | |
84 | |
52 | |
37 | |
23 |