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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sanaz
Regular Visitor

How to calculate fist occurrence

Hi all

 

I need your help/advice on how to calculate "Result-first export date" from table below.

 

Basically, For each ID on each row, it should first check through column "Export/Import" and find "EXP" and then return the minimum export date from "Date" column in the result column. Say for example: For ID= A12, first should find where is the EXP located under "Export/Import", it's on row#3&8, so the minimum Export date is on row#8, now it should fill in 3/2/2018 for all the rows that are related to ID=A12.

 

#IDExport/ImportDateResult- First export date
1C11IMP1/1/20185/2/2018
2B12IMP2/1/20192/1/2019
3A12EXP4/10/20223/2/2018
4A12IMP1/1/20183/2/2018
5B12EXP2/1/20192/1/2019
6A12IMP2/1/20183/2/2018
7C11EXP5/2/20185/2/2018
8A12EXP3/2/20183/2/2018
9A12IMP3/11/20183/2/2018

 

I kindly appreciate your time and effort here.

 

Thanks in advance,

Sanaz

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Sanaz ,

 

Check the formula.

Column = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Export/Import]="EXP"))

vjaywmsft_0-1669799738008.png

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @Sanaz ,

 

Check the formula.

Column = CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Export/Import]="EXP"))

vjaywmsft_0-1669799738008.png

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
pbi-novice
Helper I
Helper I

Hi Sanaz,

 

You can make a calculated table and relate it to this table by "ID", see screenshot below.
DAX for calculated table ("Table" is original table, "MinDate" is calculated table):

MinDate = 

GROUPBY(

FILTER(

'Table',

'Table'[Export/Import] = "EXP"

),

'Table'[ID],

'Table'[Export/Import],

"FirstExpDate" ,

MINX(

CURRENTGROUP(),

[Date]
)

)

 

pbinovice_0-1668094545877.png

pbinovice_1-1668094833337.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.