Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |