Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vm8181
Frequent Visitor

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

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

RemoteDateTimeStatus
+2778752843006-04-2024 10:52Unique
+2778752843007-04-2024 05:13Repeat
+2778752843008-04-2024 01:43Repeat
+2778752843008-04-2024 01:46Repeat
+2778752843008-04-2024 02:00Repeat
+2778752843008-04-2024 02:11Repeat
+2778752843008-04-2024 02:52Repeat
+2778752843008-04-2024 05:19Repeat
+2778752843009-04-2024 05:46Repeat
+2778752843009-04-2024 13:07Repeat
+2778752843009-04-2024 20:31Repeat
+2778752843009-04-2024 20:34Repeat
+2778752843010-04-2024 05:20Repeat
+2778752843012-04-2024 02:33Repeat
+2778752843012-04-2024 04:43Repeat
+2778752843013-04-2024 05:13Unique
+2778752843013-04-2024 05:16Repeat
+2778752843013-04-2024 05:20Repeat
+2778752843014-04-2024 05:04Repeat
+2778752843015-04-2024 03:35Repeat
+2778752843017-04-2024 04:54Repeat



1 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:

vlinyulumsft_0-1716978790185.png

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.

vlinyulumsft_1-1716978869765.png

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.

View solution in original post

4 REPLIES 4
vm8181
Frequent Visitor

Thanks for your response @ryan_mayu 

vm8181_0-1716533316611.png

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... 

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:

vlinyulumsft_0-1716978790185.png

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.

vlinyulumsft_1-1716978869765.png

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.

Hi @v-linyulu-msft ,

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
Super User
Super User

@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")
 
11.PNG
 
pls see the attachment below




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

Proud to be a Super User!




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.