Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help

7 Replies

  • danextian's avatar
    danextian
    Super User
    5 years ago

    hI Anonymous ,

    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.

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I just provided more details!

     

    thank you!

  • danextian's avatar
    danextian
    Super User
    5 years ago

    hI Anonymous ,
    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
)