Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have a table that has data like the below one:
Date | Name |
30-11-2022 | Denny |
30-11-2022 | Cathy |
30-11-2022 | Andrew |
30-11-2022 | Denny |
30-11-2022 | Gagan |
30-11-2022 | Cathy |
30-11-2022 | Siri |
31-12-2022 | Cathy |
31-12-2022 | Vivek |
31-12-2022 | Sunek |
31-12-2022 | Bastien |
31-12-2022 | Andrew |
31-12-2022 | Bastien |
31-12-2022 | Philip |
31-12-2022 | Andrew |
31-12-2022 | John |
31-12-2022 | Creda |
31-12-2022 | Barbara |
31-01-2023 | John |
31-01-2023 | Philip |
31-01-2023 | Creda |
31-01-2023 | Siri |
31-01-2023 | Siri |
31-01-2023 | John |
I need to create a measure , say "Unique count", that will mark the unique values in a column and the first instance of duplicates with 1 and the rest blank.
Please note that I have a 'Date' filter(Slicer) that user can select.
So the expected output of the above table will be as below:
1) All dates selected
Date | Name | Unique count |
30-11-2022 | Andrew | 1 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Barbara | 1 |
31-12-2022 | Bastien | 1 |
31-12-2022 | Bastien | 0 |
30-11-2022 | Cathy | 1 |
30-11-2022 | Cathy | 0 |
31-12-2022 | Cathy | 0 |
31-12-2022 | Creda | 1 |
31-01-2023 | Creda | 0 |
30-11-2022 | Denny | 1 |
30-11-2022 | Denny | 0 |
30-11-2022 | Gagan | 1 |
31-12-2022 | John | 1 |
31-01-2023 | John | 0 |
31-01-2023 | John | 0 |
31-12-2022 | Philip | 1 |
31-01-2023 | Philip | 0 |
30-11-2022 | Siri | 1 |
31-01-2023 | Siri | 0 |
31-01-2023 | Siri | 0 |
31-12-2022 | Sunek | 1 |
31-12-2022 | Vivek | 1 |
TOTAL | 12 |
2) Date = 30-11-2022
Date | Name | Unique count |
30-11-2022 | Andrew | 1 |
30-11-2022 | Cathy | 1 |
30-11-2022 | Cathy | 0 |
30-11-2022 | Denny | 1 |
30-11-2022 | Denny | 0 |
30-11-2022 | Gagan | 1 |
30-11-2022 | Siri | 1 |
TOTAL | 6 |
3) Date = 31-12-2022
Date | Name | Unique count |
31-12-2022 | Andrew | 1 |
31-12-2022 | Andrew | 0 |
31-12-2022 | Barbara | 1 |
31-12-2022 | Bastien | 1 |
31-12-2022 | Bastien | 0 |
31-12-2022 | Cathy | 1 |
31-12-2022 | Creda | 1 |
31-12-2022 | John | 1 |
31-12-2022 | Philip | 1 |
31-12-2022 | Sunek | 1 |
31-12-2022 | Vivek | 1 |
TOTAL | 9 |
4) Date = 31-01-2023
Date | Name | Unique count |
31-01-2023 | Creda | 1 |
31-01-2023 | John | 1 |
31-01-2023 | John | 0 |
31-01-2023 | Philip | 1 |
31-01-2023 | Siri | 1 |
31-01-2023 | Siri | 0 |
To summarize, for each kind of filter user applies the measure should dynamically calculate the same way for the selected values.
I had posted the scenario earlier but I am trying to rephrase it for a better understanding of the issue i am facing.
Any help on the above scenario is appreciated.
Thanks a lot.
Solved! Go to Solution.
hi @Anonymous
try like:
1) add an index like:
2) plot all the column with a measure like:
measure =
VAR _index = MAX(TableName[Index])
VAR _name =MAX(TableName[Name])
VAR _indexmin =
MINX(
FILTER(
ALLSELECTED(TableName),
TableName[Name] =_name
),
TableName[Index]
)
RETURN
IF(
_indexmin = _index,1,0
)
it worked like:
p.s. An index column is needed to differeniate identical rows, otherwise duplicate rows will be hiden in visual.
hi @Anonymous
try like:
1) add an index like:
2) plot all the column with a measure like:
measure =
VAR _index = MAX(TableName[Index])
VAR _name =MAX(TableName[Name])
VAR _indexmin =
MINX(
FILTER(
ALLSELECTED(TableName),
TableName[Name] =_name
),
TableName[Index]
)
RETURN
IF(
_indexmin = _index,1,0
)
it worked like:
p.s. An index column is needed to differeniate identical rows, otherwise duplicate rows will be hiden in visual.
Thanks a lot. That worked for me!!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |