Remove duplicates in DAX

[Anonymous]

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 

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?

Thanks in advance

[danextian]

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
)

[Anonymous]

I just provided more details!

thank you!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

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

[vivran22]

Hello @Anonymous ,

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

[edhans]

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.

[danextian]

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.