Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

DAX Measure that marks unique values in a column and the first instance of duplicates with 1

Hi All,
I have a table that has data like the below one:

DateName
30-11-2022Denny
30-11-2022Cathy
30-11-2022Andrew
30-11-2022Denny
30-11-2022Gagan
30-11-2022Cathy
30-11-2022Siri
31-12-2022Cathy
31-12-2022Vivek
31-12-2022Sunek
31-12-2022Bastien
31-12-2022Andrew
31-12-2022Bastien
31-12-2022Philip
31-12-2022Andrew
31-12-2022John
31-12-2022Creda
31-12-2022Barbara
31-01-2023John
31-01-2023Philip
31-01-2023Creda
31-01-2023Siri
31-01-2023Siri
31-01-2023John


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 

DateNameUnique count
30-11-2022Andrew1
31-12-2022Andrew0
31-12-2022Andrew0
31-12-2022Barbara1
31-12-2022Bastien1
31-12-2022Bastien0
30-11-2022Cathy1
30-11-2022Cathy0
31-12-2022Cathy0
31-12-2022Creda1
31-01-2023Creda0
30-11-2022Denny1
30-11-2022Denny0
30-11-2022Gagan1
31-12-2022John1
31-01-2023John0
31-01-2023John0
31-12-2022Philip1
31-01-2023Philip0
30-11-2022Siri1
31-01-2023Siri0
31-01-2023Siri0
31-12-2022Sunek1
31-12-2022Vivek1
 TOTAL12


2) Date = 30-11-2022

DateNameUnique count
30-11-2022Andrew1
30-11-2022Cathy1
30-11-2022Cathy0
30-11-2022Denny1
30-11-2022Denny0
30-11-2022Gagan1
30-11-2022Siri1
 TOTAL6


3) Date = 31-12-2022

DateNameUnique count
31-12-2022Andrew1
31-12-2022Andrew0
31-12-2022Barbara1
31-12-2022Bastien1
31-12-2022Bastien0
31-12-2022Cathy1
31-12-2022Creda1
31-12-2022John1
31-12-2022Philip1
31-12-2022Sunek1
31-12-2022Vivek1
 TOTAL9


4) Date = 31-01-2023

DateNameUnique count
31-01-2023Creda1
31-01-2023John1
31-01-2023John0
31-01-2023Philip1
31-01-2023Siri1
31-01-2023Siri0


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.

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @Anonymous_mdky 

try like:

1) add an index like:

FreemanZ_0-1678285839865.png

 

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:

FreemanZ_1-1678285909442.png

 

FreemanZ_2-1678285921868.png

 

p.s. An index column is needed to differeniate identical rows, otherwise duplicate rows will be hiden in visual. 

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @Anonymous_mdky 

try like:

1) add an index like:

FreemanZ_0-1678285839865.png

 

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:

FreemanZ_1-1678285909442.png

 

FreemanZ_2-1678285921868.png

 

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!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors