Write a dax to get "unique" for first entry of Remote within every 7 days for same remote.

[vm8181]

I want a calculated column as below. I have written a dax for that which is not satisfying the requirement

Status =
VAR CurrentPhone = 'Correct Example'[Remote]
VAR CurrentDate = 'Correct Example'[DateTime]
VAR result =
IF(
CurrentDate =
CALCULATE(
MIN('Correct Example'[DateTime]),
FILTER(
ALL('Correct Example'),
'Correct Example'[Remote] = CurrentPhone &&
'Correct Example'[DateTime] >= CurrentDate - 7
)
),
1, 0
)
RETURN
IF(
result = 1,
"Unique",
"Repeat"
)

A quick response will be very helpful!
Thankyou so much,

Below I have mentioned the example

Remote	DateTime	Status
+27787528430	06-04-2024 10:52	Unique
+27787528430	07-04-2024 05:13	Repeat
+27787528430	08-04-2024 01:43	Repeat
+27787528430	08-04-2024 01:46	Repeat
+27787528430	08-04-2024 02:00	Repeat
+27787528430	08-04-2024 02:11	Repeat
+27787528430	08-04-2024 02:52	Repeat
+27787528430	08-04-2024 05:19	Repeat
+27787528430	09-04-2024 05:46	Repeat
+27787528430	09-04-2024 13:07	Repeat
+27787528430	09-04-2024 20:31	Repeat
+27787528430	09-04-2024 20:34	Repeat
+27787528430	10-04-2024 05:20	Repeat
+27787528430	12-04-2024 02:33	Repeat
+27787528430	12-04-2024 04:43	Repeat
+27787528430	13-04-2024 05:13	Unique
+27787528430	13-04-2024 05:16	Repeat
+27787528430	13-04-2024 05:20	Repeat
+27787528430	14-04-2024 05:04	Repeat
+27787528430	15-04-2024 03:35	Repeat
+27787528430	17-04-2024 04:54	Repeat

[vm8181]

Thanks for your response @ryan_mayu 

I have tried you DAX but it's not working as expected I have attached a sample file below you can refer for more information.

https://1drv.ms/x/c/e600fca21b43017e/EWvJDQcrwBREhUEjUqHIIBQB16hZmWt-eVkx8fof_v5-xQ?e=bKzJnx&nav=MTV... 

[Anonymous · Accepted solution]

Thanks for the reply from @ryan_mayu , please allow me to provide another insight:
Hi,@vm8181 

1.First I have created the following table and the column names and data are the data you have given:

2.First, create a calculated column for easy grouping later based on the seven days:

wn1 = 
VAR ct=CALCULATE(MIN('Correct Example'[DateTime]),ALLEXCEPT('Correct Example','Correct Example'[Remote]))
    VAR ds=DATEDIFF(ct,'Correct Example'[DateTime],DAY)
    RETURN INT(ds/7)

3. Below are the calculated column I've created for your needs:

Status1 = 
VAR _table = CALCULATETABLE(SUMMARIZE(ALL('Correct Example'),'Correct Example'[Remote],'Correct Example'[DateTime],'Correct Example'[Status],
    "wn",
    VAR ct=CALCULATE(MIN('Correct Example'[DateTime]),ALLEXCEPT('Correct Example','Correct Example'[Remote]))
    VAR ds=DATEDIFF(ct,'Correct Example'[DateTime],DAY)
    RETURN INT(ds/7),
    "ds",
        VAR ct=CALCULATE(MIN('Correct Example'[DateTime]),ALLEXCEPT('Correct Example','Correct Example'[Remote]))
    RETURN DATEDIFF(ct,'Correct Example'[DateTime],DAY)
)
)
RETURN  

if ('Correct Example'[DateTime]=MINX(FILTER(_table,[ds]>=7*[wn]&&[ds]<=7*([wn]+1)&&[wn]=EARLIER([wn1])),'Correct Example'[DateTime]),"Unique","Repeat")

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vm8181]

Hi @Anonymous ,

Thanks for your valuable response and effort🙏 The above solution in dax quite easy to understand as compare to power query.
I have received the solution in power query from other member. Below is the link of that solution. 
https://community.fabric.microsoft.com/t5/Desktop/Mark-the-first-date-for-each-remote-as-quot-Unique...

[ryan_mayu]

@vm8181 

here is a workaround for you

create two columns

Column =

var _min=int(minx(FILTER('Table','Table'[Remote]=EARLIER('Table'[Remote])),'Table'[DateTime]))

return if(mod((int('Table'[DateTime])-_min),7)=0,"Unique","Repeat")

Column 2 = if('Table'[Column]="Unique" && 'Table'[DateTime]=CALCULATE(min('Table'[DateTime]),ALLEXCEPT('Table','Table'[Remote],'Table'[Date])),"Unique","Repeat")

pls see the attachment below