cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Remove duplicates in DAX

Hi Guys,

 

Edit: Proving more informations

 

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

TABLE 1 FACT 

TimeIDIDstart timefinish time
1A11:00 AM11:15 AM
1A12:00 PM12:15 AM
2B5:00 PM5:15 PM
3C7:00 PM9:00 PM

TABLE 2 TIME TABLE

TimeIDTimeHOUR
1Morning11:00 AM
2Afternon12:00PM
3Evening6: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. 

 

TimeIDTimeID
1MorningA
1AfternonA
2AfternoonB
3EveningC

 

 

The result I need

 

TimeIDTimeID
1MorningA
2AfternoonB
3EveningC

 

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?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Remove duplicates in DAX

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: Remove duplicates in DAX

hI @fabnishi1207 ,

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User III
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:

edhans_0-1601599829671.png

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 I answer your question? Mark my post as a solution!
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
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
Connect on LinkedIn
Follow on Twitter

Highlighted
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Regular Visitor

Re: Remove duplicates in DAX

I just provided more details!

 

thank you!

Highlighted
Super User II
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
)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User III
Super User III

Re: Remove duplicates in DAX

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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