cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Remove duplicates in DAX

Hi Guys,

I have this two different tables, the fact table (many ids) and my time table (unique id)

TABLE 1 FACT

 TimeID ID start time finish time 1 A 11:00 AM 11:15 AM 1 A 12:00 PM 12:15 AM 2 B 5:00 PM 5:15 PM 3 C 7:00 PM 9:00 PM

TABLE 2 TIME TABLE

 TimeID Time HOUR 1 Morning 11:00 AM 2 Afternon 12:00PM 3 Evening 6:00 PM

My result is duplication ID A because I have ID A for Morning and Afternon, however I need to use only the first value which is morning.

 TimeID Time ID 1 Morning A 1 Afternon A 2 Afternoon B 3 Evening C

The result I need

 TimeID Time ID 1 Morning A 2 Afternoon B 3 Evening C

I need that the ID (A) only appears once for morning which is where the it starts.

I need to use dax calculated measure for this, is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Remove duplicates in DAX

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Highlighted
Super User II

## Re: Remove duplicates in DAX

Does your table indicate which value in Time column comes first for each ID? You may create a formula that returns only the value for Morning but Morning may not alwasy be the first value.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User III

## Re: Remove duplicates in DAX

It is possible, but not the way you have your data. If I use the MIN() function, I get this:

MAX() would have returned Morning for ID1, but the problem is Power BI doesn't know what "Morning" and "Afternoon" are. Instead, MIN/MAX are working alphabetically here.

And since your data isn't alphabetical, MIN/MAX won't work. For example, MIN returns afternoon in this example, but if you had Morning and Evening for some IDs, MIN there would return Evening because E is before M. You need another field to define the order to take your data and pull that. Or convert Morning, Afternoon, Evening into 8am, 12 noon, 6pm as a time field. Then MIN/MAX would work fine.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User I

## Re: Remove duplicates in DAX

Hello @fabnishi1207 ,

The output required is not clear from your description. Please share more details on the output.

Cheers!
Vivek

Blog: vivran.in/my-blog

Highlighted
Super User III

## Re: Remove duplicates in DAX

Hi,

Share the base data tables from where you created this output.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Regular Visitor

## Re: Remove duplicates in DAX

I just provided more details!

thank you!

Highlighted
Super User II

## Re: Remove duplicates in DAX

hI @fabnishi1207 ,
You can try this formula to be used as a visual filter where value = 1

``````FirstValue =
IF (
SELECTEDVALUE ( 'FACT'[start time] )
= CALCULATE ( MIN ( 'FACT'[start time] ), ALLEXCEPT ( 'FACT', 'FACT'[ID] ) ),
1,
0
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User III

## Re: Remove duplicates in DAX

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors