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
Aborrochd
Frequent Visitor

Find the Earliest Start based on a a different column condition

Hi,

 

I am having two linked table and ultimetly I want to find the duration time for each project depending on the location of the project (defined by the column sk_lieu)

I have a column called Quantité Réalisée describing the realised effort done.

I want first to find the earliest date registered for that column (Quantité réalisée). the column Quantité réalisé may have zéro values.

So For Each [sk_lieu] (representing a city), I want a calculated measure that gives back the earliest start date that there is a registerd value in the column [Quantité réalisé] <>0. (I can Use a slicer the [sk_lieu], so it doenst matter if the formula doesn't contain a call for the [sk_lieu].

Then I want a measure to calculate the duration up to today since that task has started

 

 

Screenshot 2021-01-12 101758.jpgScreenshot 2021-01-12 101923.jpg

1 ACCEPTED SOLUTION
Watsky
Solution Sage
Solution Sage

Hey @Aborrochd  

 

I'm assuming that the sk_temps is the numerical version of the date. If this is the case there's a couple of ways I would approach this.

 

The first way I would approach this is

 

1.) Create a new column in your date table which is a duplicated copy of the date. Then I would change the type to whole number so now I have a representation of the date as a number (sk_temps).

 

sktemp.png

 

2.) Disable the relationship between the Date table and Dim_Suivi_Project. Join Date to Fait_Suivi_Projects on sk_temps and sk_temps.

skjoin.png

3.) Create this measure:

Min Date = 
CALCULATE(MIN('Date'[Date Range]),'Fait_Suivi_Projects'[Quantité réalisée]<>0)

4.) Create this measure: 

Duration = 
DATEDIFF([Min Date],TODAY(),DAY)

(This assumes you're counting your duration by days)

 

This is what it ends up looking like:

skres.png

If you really have to keep your joins the way they currently are in your model then keep everything the same except change your crossfilter join between Dim_Suivi_Project and Fait_Suivi_Project to both.

 

skres2.png 

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

View solution in original post

1 REPLY 1
Watsky
Solution Sage
Solution Sage

Hey @Aborrochd  

 

I'm assuming that the sk_temps is the numerical version of the date. If this is the case there's a couple of ways I would approach this.

 

The first way I would approach this is

 

1.) Create a new column in your date table which is a duplicated copy of the date. Then I would change the type to whole number so now I have a representation of the date as a number (sk_temps).

 

sktemp.png

 

2.) Disable the relationship between the Date table and Dim_Suivi_Project. Join Date to Fait_Suivi_Projects on sk_temps and sk_temps.

skjoin.png

3.) Create this measure:

Min Date = 
CALCULATE(MIN('Date'[Date Range]),'Fait_Suivi_Projects'[Quantité réalisée]<>0)

4.) Create this measure: 

Duration = 
DATEDIFF([Min Date],TODAY(),DAY)

(This assumes you're counting your duration by days)

 

This is what it ends up looking like:

skres.png

If you really have to keep your joins the way they currently are in your model then keep everything the same except change your crossfilter join between Dim_Suivi_Project and Fait_Suivi_Project to both.

 

skres2.png 

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

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.