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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Find match in previous value

Hi, I'm looking for a solution for the below issue,

 

I have a table where in every day I get new data post refresh, I want to check the data of a perticular column eg: Name in the below table and see if this exists for previously (previous days) and if there is a match then I would like to have a Yes/No column defined as out put column

 

Which function can I use to attain this?

 

Note: I don't have 2 table data its only one table

 

DateNameCity
06-01-2021SUDHAKARBangalore
07-01-2021SWETHASRIUS
08-01-2021SQUIRESASIA
09-01-2021CIAMPAEurope
10-01-2021ONALFOBangalore
11-01-2021SHRISUNDERUS
12-01-2021DAWOODASIA
13-01-2021SHEIKHEurope
14-01-2021GOYALBangalore
15-01-2021CHANDHOKEUS
16-01-2021DASASIA
17-01-2021INDORIAEurope
18-01-2021JOSHIBangalore
19-01-2021KHOCHAREUS
20-01-2021MISRAASIA
21-01-2021SUDERSANEurope
22-01-2021WADHWANIBangalore
23-01-2021DAWOODUS
24-01-2021SINGHASIA
25-01-2021DESAIEurope
26-01-2021RAWATBangalore
27-01-2021AKHTERUS
28-01-2021KHANASIA
29-01-2021MUZAMMILEurope
30-01-2021RAFIQBangalore
31-01-2021SIDDIQIUS
01-02-2021SOHAILASIA
02-02-2021PATELEurope
03-02-2021NAYYARBangalore
04-02-2021PANDEYUS
05-02-2021BALAKRISHNANASIA
06-02-2021GOVEKAAREurope
07-02-2021SOMACHUDANBangalore
08-02-2021JEWALIKARUS
09-02-2021ACUÑAASIA
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

 

Previous days Name exist? CC =
VAR _currentdate = Data[Date]
VAR _previousdaystablenamecolumn =
SUMMARIZE ( FILTER ( Data, Data[Date] < _currentdate ), Data[Name] )
RETURN
IF ( Data[Name] IN _previousdaystablenamecolumn, "Yes", "No" )
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thanks that works. I have a query regards to report. 

 

If I have to create a measure to display the count as to number to names that has been found previously as of today (I want to consider the last refresh day or the recent date in date column for this calculation)

 

 

Hi @Anonymous,

Perhaps you can try to use today function to replace the variable with the current date and use it to calculate. Then the calculated result will auto changes based on system DateTime. 

Notice: power bi data model does not include cache history records features, these results will be replaced every time the system datetime changes.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks for picking up my query, Appreciate it.

 

I want to know if one more time Dawood name appears will the previous value become "No" and current date value becomes "Yes" ?

Thanks for the feedback.

If one more Dawwod is added, 1st previous Dawood already has the 2nd previous Dawood.

So, I think, it will still show yes.

You can try.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Picture1.png

 

Previous days Name exist? CC =
VAR _currentdate = Data[Date]
VAR _previousdaystablenamecolumn =
SUMMARIZE ( FILTER ( Data, Data[Date] < _currentdate ), Data[Name] )
RETURN
IF ( Data[Name] IN _previousdaystablenamecolumn, "Yes", "No" )
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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